MySQL join based on where from 2nd table

0pizxfdo 于 2022-12-22 发布在 Mysql

关注(0)|答案(2)|浏览(156)

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.

mysql

来源：https://stackoverflow.com/questions/74868688/mysql-join-based-on-where-from-2nd-table

2条答案

按热度按时间

kt06eoxx1#

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

赞(0）回复(0）举报 2022-12-22

3bygqnnd2#

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

赞(0）回复(0）举报 2022-12-22

我来回答

MySQL join based on where from 2nd table

2条答案

相关问题

热门标签

最新问答