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

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.

mysql
sql

2012-04-03 22:41
by Ray

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

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