Question 21674 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL join based on where from 2nd table

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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search