Using the results from 2 tables to get info from a 3rd one

Go To StackoverFlow.com

2

hi i have tables like this:

Persons: person_id, name then i have many langauge tables, that contain the languages people speak, the tables themselves only have the IDs. for example:

english: person_id

then I also have a table that contains what schools they teach in, broken down to tables for example:

havard: person_id

To get those people that teach at havard and also speak english, I use the following query:

SELECT * FROM english LEFT JOIN havard.person_id = english.person_id

this will return the id of the person that can speak english and teaches at havard. How can I use that result to get the those people's name from the persons table? It's easy peasy with php, but I'm wondering if it's doable with mysql as well.

2012-04-03 22:41
by Ray


2

Here's a query that I believe answers your question:

SELECT person.name
FROM 
    english 
    JOIN harvard ON havard.person_id = english.person_id
    JOIN persons ON persons.person_id = harvard.person_id

However, I would STRONGLY recommend against your current table structure. You shouldn't have many tables for languages, and many tables for schools. This will completely unmaintainable...

Instead, you should have a single language table, and a single school table. That way a new langauge or school being added to your table doesn't requrie schema or code changes.

To handle the many-to-many relationships, you could use a schema similar to the following:

Language

  • ID
  • Name

School

  • ID
  • Name

Language_Person

  • Language_ID
  • Person_ID

School_Person

  • School_ID
  • Person_ID
2012-04-03 22:49
by Michael Fredrickson
I agree that this is a much better schem - JDD 2012-04-03 22:58
Thanks man, good advice - Ray 2012-04-03 23:10
Does it change anything that a langauge can be basically spoken by indefinite number of people, also a teacher(in theory of course) can teach at indefinite number of schools - Ray 2012-04-03 23:19
@Ray I would say that reinforces your need for a table structure similar to what I'm suggesting.. - Michael Fredrickson 2012-04-03 23:26
Thanks a lot for your input, ill research into this a bit more in that case - Ray 2012-04-04 00:31
One last question though, won't that result in redundant data? Like: School person: sid1 - pid1; sid1 - pid2; sid1 - pid3; sid2 - pid1; and so on. - Ray 2012-04-04 08:38
@Ray Only the foreign keys (sid1 and pid1) exist more than once, but none of the other data that depends on the key is duplicated, so this is a normalized approach. You should give this article a quick read, which describes many-to-many relationships and how to model them.Michael Fredrickson 2012-04-04 14:17
Thank you. I'm now working on the selects : - Ray 2012-04-04 15:29


1

SELECT person.name FROM (english INNER JOIN harvard ON havard.person_id = english.person_id) INNER JOIN persons ON persons.person_id = harvard.person_id WHERE persons.person_id = "

2012-04-03 22:53
by JDD
thanks for the input - Ray 2012-04-03 23:05