MySQL join based on where from 2nd table

Question

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.

slaakso · Accepted Answer · 2022-12-20 21:23:50Z

3

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

answered Dec 20, 2022 at 21:23

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nick Over a year ago

I just totally missed the fact that I can do a self-join. Thanks @slaakso

Zak · Accepted Answer · 2022-12-20 21:23:57Z

0

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

answered Dec 20, 2022 at 21:23

Zak

7,5812 gold badges29 silver badges62 bronze badges

1 Comment

ysth Over a year ago

I see no benefit of junction tables here; a docs row has a carid, not multiple ones, and a car has a parent car, not multiple ones

Collectives™ on Stack Overflow

MySQL join based on where from 2nd table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related