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:
then I also have a table that contains what schools they teach in, broken down to tables for example:
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.
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:
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 = "