CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Elad Benda
Elad Benda

Reputation: 36674

How to sql select a product with two status?

I have a Products table

with ProductId and StatusId columns

How can i select all the products which have StatusId 1 and 2 (two rows) ?

Upvotes: 2

Views: 7977

Answers (3)

Meherzad
Meherzad

Reputation: 8563

Try this query to get records for product having both the status 1 and 2

SELECT DISTINCT a.ProductId 
FROM Products a, Products b 
WHERE a.ProductId = b.ProductId 
  AND a.StatusId = 1 
  AND b.StatusId = 2;

USING ANSI JOIN

SELECT DISTINCT a.ProductId 
FROM Products a INNER JOIN Products b 
ON a.ProductId = b.ProductId 
WHERE a.StatusId = 1 
  AND b.StatusId = 2;

Fiddle

Upvotes: 2

Kaf
Kaf

Reputation: 33849

To select Products with both status (1,2):

Sql-Server fiddle demo

SELECT ProductId 
FROM products
WHERE status IN (1, 2)
GROUP BY ProductId
HAVING COUNT(distinct status) = 2

Upvotes: 7

aF.
aF.

Reputation: 66727

SELECT DISTINCT a.ProductId 
FROM Products a
INNER JOIN Products b ON a.ProductId = b.ProductId
WHERE a.StatusId IN (1, 2)

Upvotes: 0

Related Questions