CODEWITHSUNDEEP
sqlsql-server
Adi
Adi

Reputation: 329

multiple condition in same column

i have a table dir having directors

insert into dir values ('d1','mov1','us',3);
insert into dir values ('d1','mov1','ind',3);
insert into dir values ('d2','mov2','uk',4);
insert into dir values ('d2','mov3','ind',3);

want those only which are present in all three us ,ind and uk.

my code:

select  directornaame from (
     Select Distinct directornaame, country
       From
       dir
       Where country = 'ind' or country='uk' or country='us'
) as  s
Group By directornaame
Having count(directornaame)>=2

but it is should not give any result but it is giving both d1 and d2 in output window.

THanks!!

Upvotes: 0

Views: 33

Answers (1)

Ilyes
Ilyes

Reputation: 14928

It's a simple query, no need for DISTINCT in the SubQuery and no need for a SubQuery at all

SELECT DirectorName
FROM Dir
WHERE Country IN('UK', 'IND', 'US') 
GROUP BY DirectorName
HAVING COUNT(DISTINCT Country) = 3;

it works but is there any workaround where we hard code the values of country

You could use a Table-Valued parameter as

DECLARE @T TABLE (Country VARCHAR(3));

INSERT INTO @T(Coutry) VALUES
('UK'), ('IND'), ('US');

SELECT DirectorName
FROM Dir
WHERE Country IN(SELECT Country FROM @T) 
GROUP BY DirectorName
HAVING COUNT(DISTINCT Country) = 3;

Upvotes: 1

Related Questions