Click here to Skip to main content
14,868,587 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have simple query, i am using IN keyword I want to show only rows that have all the item.

Example : i have two table 1)Video 2) Category

select Video.videoid, title, categoryid from Video join Category on Video.portalid=Category.portalid where Category.CategoryId in (1,2,3)

This query return all the record that have category id 1 or 2 or 3 etc. but I want to show only rows that have all those three categoryId values.

I am also using group by but i think i am missing something..

select Video.videoid, title, categoryid from Video join Category on Video.portalid=Category.portalid where Category.CategoryId in (1,2,3)
group by Video.VideoId,title,categoryid
having count(distinct categoryid ) = 3

I tried some other way but I did not succeed yet.
Posted

I'm not sure this is the easiest but it's the first that comes to mind. Join 3 times to Category table to make sure it is in there all three times. For example:
SQL
SELECT *
FROM Video v
JOIN Category c1 ON v.portalid = c1.portalid AND c1.CategoryID = 1
JOIN Category c2 ON v.portalid = c2.portalid AND c2.CategoryID = 2
JOIN Category c3 ON v.portalid = c3.portalid AND c3.CategoryID = 3


Depending on what data you are pulling this may cause too many duplicate record so you may also need to put DISTINCT in your SELECT.
   
Comments
Member 11324668 30-Nov-15 9:13am
   
CategoryId is dynamic, i will get CategoryId by function.example categoryid = 1,2,3.
so we can not do multiple join or union.
You need to use HAVING

SQL
SELECT V.VideoID, COUNT(CategoryID) as CNT
FROM Video v
WHERE v.CategoryID IN (1,2,3)
GROUP BY V.VIdeoID
HAVING COUNT(CategoryID) = 3 --COUNT from list of necessary categories. (1,2,3)



In your example (you should remove categoryid from the group by. That's why it is give you wrong answer:

SQL
select Video.videoid, title 
from Video join Category on Video.portalid=Category.portalid where Category.CategoryId in (1,2,3)
group by Video.VideoId,title
having count(distinct categoryid ) = 3
   
v3
Comments
Member 11324668 1-Dec-15 2:03am
   
thank you nashi :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900