CODEWITHSUNDEEP
sql-server
Yunus Ahamed
Yunus Ahamed

Reputation: 13

create sql select query filter with two value on same field with and condition

This is my table :

Srlno   Item Code   Category Code   
1       BT/000001     CLR   
2       BT/000001     SIZ   
3       BT/000001     WAT   
1       BT/000002     Swt   
2       BT/000002     Mol   
3       BT/000002     SIZ

I use this Query:

SELECT Item_Code
FROM T_SubCatogoryDtls   
WHERE SubCat_code in (N'SIZ' , N'Swt')
GROUP BY Item_Code

but the result is:

BT/000001
BT/000002

I need BT/000002 because category code Siz and Swt common in this item code what is correct query

Upvotes: 1

Views: 37

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72215

You have to add a HAVING clause:

SELECT Item_Code
FROM T_SubCatogoryDtls   
WHERE SubCat_code in (N'SIZ' , N'Swt')
GROUP BY Item_Code
HAVING COUNT(DISTINCT SubCat_code) = 2

HAVING filters out any Item_Code groups not having both codes, i.e. 'SIZ' and 'Swt'.

Upvotes: 2

Related Questions