CODEWITHSUNDEEP
mysqlsql
Ray
Ray

Reputation: 119

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.

Upvotes: 2

Views: 62

Answers (2)

JDD
JDD

Reputation: 134

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 = "

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

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

Upvotes: 2

Related Questions