Masterid CC CLA DES NLCLA NLDES
-------------------------------------
53006141 CN 0 0 1 1
53006141 US 1 1 1 1
53006141 UK 1 1 0 0
53006142 US 1 1 0 0
53006142 UK 1 1 0 0
53006143 CN 0 0 1 1
53006143 US 1 1 0 0
53006143 UK 1 1 0 0
From the above data I need to produce
- a list of
MasterIds where there is CC = US or CC = CN and NLCLA = 1 and NLDES = 1
The output should be
53006141
53006143
There has to be both CN and US under a MasterID.
Can someone help me to do this in SQL please?
You can do this by adding a WHERE clause which will return rows with either US or CN:
select distinct Masterid
from yourtable
where cc in ('US', 'CN')
and NLCLA = 1
AND NLDES = 1
See SQL Fiddle with Demo
If you want the result to include both the CN and US, then you can use:
select Masterid
from yourtable
where cc in ('US', 'CN')
and NLCLA = 1
AND NLDES = 1
group by masterid
having count(distinct cc) = 2
See SQL Fiddle with Demo.
Another way that this could be done is using an EXISTS to get the list of MasterIds with both the US and CN. You then place the other filters in the WHERE clause and not in the subquery.
select distinct masterid
from yourtable t1
where exists (select Masterid
from yourtable t2
where cc in ('US', 'CN')
and t1.masterid = t2.masterid
group by masterid
having count(distinct cc) = 2)
and NLCLA = 1
and NLDES = 1;
See SQL Fiddle with Demo
One way is using a CTE:
WITH CTE AS
(
SELECT Masterid,CC,CLA,DES,NLCLA,NLDES,
RN = ROW_NUMBER() OVER (PARTITION BY Masterid ORDER BY Masterid)
FROM dbo.Table
WHERE CC IN('US', 'CN')
AND NLCLA = 1
AND NLDES = 1
)
SELECT Masterid FROM CTE WHERE RN = 1
Demo ( thanks to bluefeet for fiddle-data )
Note that the ROW_NUMBER partition function would be helpful if you want to get a specific row, for example always the latest record for each Masterid. But since you haven't provided a datetime column i've just ordered arbitrarily by Masterid.
