Joining 3 tables and searching 2nd tables multiple rows

[lucius]

I have three tables:

table1 (id, name, etc), table2 (id, title, etc), table3 (table1.id, table2.id)

 

For example:

I want to pull up row in table1 (mechanisms) individually and link it to all of its relatives in table2 (parts) which is linked by table3 (which mechanisms have specific parts).

 

A row in table1 can be linked to any number of rows in table2.

 

So I want to find out what types of parts a car has, this is what I'd want to see:

------------+--------+-----------+--------+-----------+--------+-----------+---------+

| table1.id |  name  | table2.id | title  | table2.id | title  | table2.id | title  |

------------+--------+-----------+--------+-----------+--------+-----------+---------+

| 1        | car    | 3        | brakes | 8        | tires  | 5        | engine  |

------------+--------+-----------+--------+-----------+--------+-----------+---------+

 

How do I go about writing a query for this?

 

I really appreciate the help; I've been beat by this one!

[lucius]

I need to be able to search the 2nd table (parts in this example) and find what mechanisms match.

 

To get the above result, I might search for mechanisms with brakes, tires and engine, or any combination of those three.

[fenway]

What is your current query?

[kickstart]

Hi

 

In simple terms you can't do it.You would get a variable number of columns for each row.

 

You could either do:-

 

SELECT table1.id, table1.name, table2.id, table2.title

FROM table1

LEFT OUTER JOIN table3 ON table1.id = table3.table1id

LEFT OUTER JOIN table2 ON table3.table2id = table2.id

 

That would give you one row for every valid combination of tables 1 and 2.

 

You could concatenate the results up using GROUP_CONCAT, but this would result in the details being difficult to process if you needed the individual mechanisms.

 

SELECT table1.id, table1.name, GROUP_CONCAT(table2.title)

FROM table1

INNER JOIN table3 ON table1.id = table3.table1id

INNER JOIN table2 ON table3.table2id = table2.id

GROUP BY table1.id, table1.name

 

All the best

 

Keith

[lucius]

Actually, I did get it solved.  It was very simple:

 

SELECT *

FROM mechanisms as m, linked as l1, linked as l2, linked as l3

WHERE m.id = l1.mid

AND m.id = l2.mid

AND m.id = l3.mid

AND l1.partsid = 1

AND l2.partsid = 4

AND l3.partsid = 9

 

Of course I don't hand code this, rather I use a PHP foreach loop.  It's an easy way to manage many-to-many relationships.

[kickstart]

Hi

 

Trouble with that is that you need to know the number of final columns to build the piece of SQL in the first place. As that would mean a couple of seperate SQL calls it might well be more efficient to just use the group concat. Depends on what you want to do with the data once you have got it.

 

All the best

 

Keith

[lucius]

Well, no troubles in the execution.  This has worked perfectly for me.  I have my array of data and loop through it to build my SQL statement.

[kickstart]

Hi

 

No problem , just think you might be over complicating things a touch as it would probably be easier to just do a statement with a fixed JOIN to get all the different parts into an array.

 

All the best

 

Keith