Joins and Such

[shadowayex]

I'm not too adept with MySQL, so kindly correct me if I'm using the wrong terms and such.I have two tables devoted to two separate things, but are related in a many-to-many relationship (I think that's what it's called). I have a third table that has columns for ids from the first two tables. Items from the first table can be connected to multiple ones from the second table and vice versa.I've seen some fancy things done with joins, and I was wondering if I was given an id for one of the items, if I could pull out the records for all the related items of the other type.Example:I want to get records for all of things in table one that are related to table 2 according to the third table. I have the id for table two. I could query table 3 for all the records that have the matching table 2 id, and then use the table 1 ids from those records to get the table one records. Is there a way to simplify that?

[boen_robot]

That's sort of the idea of joins... to reconnect scattered data based on certain known relations, thus forming a new table.

SELECT table1.table1ID, table2.thing2, table2.thing3FROM relationshipTableINNER JOIN table1 ON relashionshipTable.table1ID = table1.table1ID

[davej]

I want to get records for all of things in table one that are related to table 2 according to the third table. I have the id for table two. I could query table 3 for all the records that have the matching table 2 id, and then use the table 1 ids from those records to get the table one records. Is there a way to simplify that?

There are two approaches; joins and subqueries. These two approaches are sort of equivalent except that sometimes a subquery can do something a join can't do and sometimes a join can do something a subquery can't do.If you have two tables and an intersection table that is a common situation;EMPLOYEE tablePROJECT tableASSIGNMENT table (intersection table)List all the employees assigned to projects that are over budget ;SELECT EmpNameFROM EMPLOYEE E, ASSIGNMENT A, PROJECT PWHERE E.EmpID = A.EmpIDAND A.ProjID = P.ProjIDAND P.Actual > P.Budgeted;or SELECT EmpNameFROM EMPLOYEEWHERE EmpID IN (SELECT EmpIDFROM ASSIGNMENTWHERE ProjID IN (SELECT ProjIDFROM PROJECTWHERE Actual > Budgeted));