multiple condition in same column

Question

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!!

Is this a duplicate of [this one](https://stackoverflow.com/questions/60332890/using-max-and-count-with-having)? Why `>= 2` and not `>= 3`? since you say `"are present in all three us ,ind and uk"` — Ilyes, Feb 21 '20 at 07:01
Does this answer your question? [using MAX() and COUNT() with HAVING](https://stackoverflow.com/questions/60332890/using-max-and-count-with-having) — Nitin Bisht, Feb 21 '20 at 07:04
count on group by colunn is 1, you should count country, and @Sami is right... — Turo, Feb 21 '20 at 07:06
problem is finding directors who are present in all three us ,ind and uk. and in data no one fulffills this condition — Adi, Feb 21 '20 at 07:11
Hint: `GROUP BY directornaame HAVING COUNT(DISTINCT country) >=3` — Ilyes, Feb 21 '20 at 07:12
it works but is there any workaround where we hard code the values of country — Adi, Feb 21 '20 at 07:24
ok but is also using the same logic, anyways thanks for the help — Adi, Feb 21 '20 at 07:43

Ilyes · Accepted Answer · 2020-02-21T07:33:27.017

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;

multiple condition in same column

1 Answers1