CODEWITHSUNDEEP
sqlsql-servert-sql
TBogdan
TBogdan

Reputation: 737

SQL Server : how to select the rows in a table with the same value on a column but some exact values on another column for the grouped rows

I have this table with some sample data:

Supplier_ID Product_ID  Stock
-----------------------------
1             272          1
1             123          5
1             567          3
1             564          3
2             272          4
2             12           3
2             532          1
3             123          4
3             272          5

I want to check the suppliers that have both products: 272 and 123, so the result would be like:

Supplier_ID
-----------
1
3

Upvotes: 3

Views: 69

Answers (4)

wumpz
wumpz

Reputation: 9131

This is how it works using set operations. IMHO a too little used feature of databases.

select Supplier_ID from table1 where product_id=272
intersect
select Supplier_ID from table1 where product_id=123

it produces as well

Supplier_ID
1
3

By the way a distinct is not needed due to intersect delivers distinct rows.

http://sqlfiddle.com/#!6/13b11/3

Upvotes: 2

Aj.na
Aj.na

Reputation: 283

Try This Code: By Using Row_number()

;WITH cte
     AS (SELECT *,
                Row_number()
                  OVER(
                    partition BY [Supplier_ID]
                    ORDER BY [Supplier_ID]) AS rn
         FROM   #Your_Table
         WHERE  Product_ID IN ( 272, 123 ))
SELECT DISTINCT Supplier_ID
FROM   cte
WHERE  rn > 1

OUTPUT:

Supplier_ID
1
3

Upvotes: 1

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

Try this code:

SELECT A.Supplier_ID FROM
 (SELECT Supplier_ID FROM Your_Table WHERE Product_ID = 272) AS A
INNER JOIN
 (SELECT Supplier_ID FROM Your_Table WHERE Product_ID = 123) AS B
   ON A.Supplier_ID = B.Supplier_ID

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

You can use GROUP BY and HAVING:

SELECT Supplier_ID
FROM your_tab
WHERE Product_ID IN (272, 123)
GROUP BY Supplier_ID
HAVING COUNT(DISTINCT Product_ID) = 2;

LiveDemo

Upvotes: 5

Related Questions