SQL Server: In with and condition

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

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 30-Nov-15 2:44am

Member 11324668

Add a Solution

2 solutions

Solution 1

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.

Posted 30-Nov-15 2:52am

ZurdoDev

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

nashi · Accepted Answer · 2015-11-30T05:11:00

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