关于一对多查询的问题
有三张表,message,list和message_list_assoc
一条message可以关联多个list保存在message_list_assoc中
如
message_id list_id
1 1
1 2
1 3
1 4
2 1
2 2
2 3
我要如何得到只跟list_id为1,2,3关联的message_id呢,也就是说查询结果是2.
大家帮帮忙啊!
[解决办法]
select message_id from message a
where list_id in(1,2,3)
group by message_id
having count(1)=3
[解决办法]
select
distinct l.message_id
from
list l
where
not exists(select 1 from list where message_id=l.message_id and list_id not in(1,2,3))
[解决办法]
select message_id from message
where exists(select * from message_list_assoc where list_id = 1 and message_list_assoc.messageid = message.messageid)
and exists(select * from message_list_assoc where list_id = 2 and message_list_assoc.messageid = message.messageid)
exists(select * from message_list_assoc where list_id = 3 and message_list_assoc.messageid = message.messageid)
[解决办法]
1 1
1 2
1 3
1 4
2 1
2 2
2 3
SELECT message_id FROM TB A WHERE
(SELECT COUNT(1) FROM TB B WHERE B.list_id=1) > 0 AND (SELECT COUNT(1) FROM TB B WHERE B.list_id=2) > 0
AND (SELECT COUNT(1) FROM TB B WHERE B.list_id=3) > 0
GROUP BY message_id HAVING COUNT(1)=3
[解决办法]
楼上的如果有重复项就不对了，改改，嘿嘿
select message_id from @message
where message_id in
(select message_id from @message
where list_id in(1,2,3)group by message_id having count(distinct list_id)=3)
group by message_id having count(distinct list_id)=3
