get all rows having common parents

Question

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 1

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

get all rows having common parents

Sign up