[@vijaygupta7059]

SELECT salesRepID
FROM Orders
group by salesRepID
having count(distinct SalesType)=1;

[@VARUNTEJA73]

with cte as
(select distinct salesrep_id,sales_type,dense_rank()over(partition by salesrep_id order by sales_type)ranks from orders)
select salesrep_id ,count(ranks)as counts
from cte group by salesrep_id having count(ranks)=1

[@rgv_rockz]

Great question and logic. Keep doing more videos on recently asked questions. Commenting first time on any youtube videos. Keep going!!

[@datasculptor2895]

Thank you. Please subscribe to my channel 🙂

[@vijay.s-ll1yq]

3rd methods
using correlated subquery
select salesrep from orders A where salestype ='International'
and not exists (select salesrep from orders b where a.salesrep =b.salesrep and salestype ='Domestic')
union
select salesrep from orders A where salestype ='Domestic'
and not exists (select salesrep from orders b where a.salesrep =b.salesrep and salestype ='International')

[@bhavanib2951]

Nice approach babu. Keep going babu

[@datasculptor2895]

I will try my best

[@DB-bk4bf]

Good question.

[@vijay.s-ll1yq]

2nd method
select * from
(select salesrep from orders where salestype ='International'
except
select salesrep from orders where salestype ='Domestic') A
union
SELECT * FROM
(select salesrep from orders where salestype ='Domestic'
except
select salesrep from orders where salestype ='International' ) B

[@vijay.s-ll1yq]

select a.salesrep from
( select * from orders where salestype ='International') A
left outer join
( select * from orders where salestype ='Domestic') B
on a.salesrep =b.salesrep
where b.salesrep is null
union
select b.salesrep from
( select * from orders where salestype ='International') A
right outer join
( select * from orders where salestype ='Domestic') B
on a.salesrep =b.salesrep
where a.salesrep is null

[@user-gq6cg3ls7f]

select SalesRepID from Orders where SalesRepID not in(
select SalesRepID from (
SELECT count(*) CNT, SalesRepID, SalesType FROM Orders
group by salesRepID, SalesType
) x
where CNT =2
)

[@user-gq6cg3ls7f]

your SalesRepId returns 3003,4004,5005,6006 so do my query. Am I missing something? not following our point. Please advise.

[@king-hc6vi]

Brother, you are using too many select statements so I dont think it is a optimized solution.
Instead you can use window functions..

[@datasculptor2895]

There are only 2. Please share solution with only one select statement

[@king-hc6vi]

​@@datasculptor2895 I don't think there is any need of the last select statement which is used as a sub query.
Which make 3 select statement in one solution.
Instead you can generate row number and keep it in CTE.
And then fetch the result using filters

[@UnrealAdi]

@@king-hc6vi Could you please provide a query? Can't be that simple without doing some sort of manipulations🤔

[@king-hc6vi]

WITH CTE AS (SELECT salesrepID FROM orders, COUNT() OVER (PARTITION BY salesrepID order by invoiceID) as Total_count),
SELECT salesrepID from CTE where Total_count 2; ​@@UnrealAdi

[@king-hc6vi]

​@@UnrealAdi WITH CTE AS (SELECT salesrepID FROM orders, COUNT() OVER (PARTITION BY salesrepID order by invoiceID) as Total_count),
SELECT salesrepID from CTE where Total_count 2;