fred4993 fred4993 - 4 months ago 16
SQL Question

get all rows having common parents

id  parent_id child_id
1 1 1
2 2 2
3 2 2
4 1 1


I have a table from which i need to get the common values from data when i query it with id... for eg if id=2 and id=3 then return


id parent_id
2 2
3 2


i have tried this after hunting a lot through various examples :

SELECT ta.user_id,ta.interest_parent_id,ta.interest_child_id
FROM user_interest ta
WHERE ta.user_id=2 AND
(SELECT COUNT(*) FROM user_interest tb
WHERE ta.interest_parent_id=tb.interest_parent_id
AND tb.user_id=3 )>1


but it responds with only:


id parent_id
2 2


any help :( im using a mysql database with php/codeigniter to do the scripting

Answer

You can give it a try:

SELECT 
tOne.id,
tOne.parent_id
FROM 
(
    SELECT 
    *
    FROM user_interest A 
    WHERE A.id IN (2,3) 
) tOne
INNER JOIN 

(
    SELECT 
    *
    FROM user_interest A 
    WHERE A.id IN (2,3) 

) tTwo
ON tOne.parent_id = tTwo.parent_id
AND tOne.id <> tTwo.id
ORDER BY tOne.parent_id;

SQL FIDDLE DEMO

Any suggestion towards optimization of the query is welcome.

EDIT: SQL FIDDLE

Comments