id parent_id child_id
1 1 1
2 2 2
3 2 2
4 1 1
id parent_id
2 2
3 2
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
id parent_id
2 2
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;
Any suggestion towards optimization of the query is welcome.
EDIT: SQL FIDDLE