MySQL join based on where from 2nd table-CodePudding

I have two tables as follows:

docs

id	carid	name
1	1	doc1
2	1	doc2
3	2	doc3
4	1	doc4
5	5	doc5

cars

carid	parentid	name
1	4	car1
2	5	car2
3	4	car3
4	4	car4
5	5	car5

Question: I want to write a query in mysql where I can pass the carid in where clause and get all the rows from docs table where the parentid is same as that of the passed carid.

Desired Outcome If I pass carid=3 then the rows 1,2,4 from docs table should be returned as the parentid is 4 for carids 1,3,4. Simillarly, If I pass carid=2 then the rows 3,5 from docs table should be returned as the parentid is 5 for carids 2.5.

CodePudding user response：

You need to join the cars-table twice. First for the condition and second for the parent:

select d.*
from cars c
  join cars p on p.parentid=c.parentid
  join docs d on d.carid=p.carid
where c.carid=3

CodePudding user response：

You're thinking about this a little wrong in the aspect of a relational database .. You SHOULD have 4 tables:

docs

doc_id	name
1	doc1
2	doc2
3	doc3
4	doc4
5	doc5

cars

car_id	name
1	car1
2	car2
3	car3
4	car4
5	car5

cars_to_docs

car_id	doc_id
1	1
1	2
1	4
2	3
5	5

parents_to_car

car_id	parent_id
1	4
2	5
3	4
4	4
5	5

Then you could simply use a basic JOIN

SELECT b.doc_id FROM test.docs a

LEFT JOIN test.cars_to_docs b
ON a.doc_id = b.car_id

LEFT JOIN test.parents_to_car c
ON c.car_id = b.car_id

LEFT JOIN test.cars d
ON c.car_id = d.car_id

WHERE c.parent_id = (SELECT parent_id FROM test.parents_to_car WHERE car_id = 3)

This will give you your output of 1,2,4