php - 获取具有共同 parent 的所有行

Question

id  parent_id child_id1    1               12    2               23    2               24    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_id2    23    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 

但它只响应:

id  parent_id2    2

任何帮助:( 我使用带有 php/codeigniter 的 mysql 数据库来编写脚本

Answer 1

你可以试一试:

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

欢迎提出任何优化查询的建议。

编辑:SQL FIDDLE