September 17, 2014 at 5:27 am
Tablename Consumer:
ID Resource
119319004 RG
119319004 MI
119319005 RG
119319005 MI
119319005 BT
119319006 RG
I want id whose Resource is only RG and MI that's(119319004)
Output
119319004
Please reply..
September 17, 2014 at 5:36 am
Based on what you said:-
SELECT
ID
FROM
dbo.[Consumer]
WHERE
ID = 119319004
AND
Resource = 'RG'
September 17, 2014 at 6:02 am
That dataset was just an example.I need only those id which has MI and RG both
September 17, 2014 at 6:39 am
How about:-
SELECT
ID
FROM
dbo.[Consumer]
WHERE
Resource in ('RG','MI')
September 17, 2014 at 7:20 am
This query will return those id also which contain resource type BT (ex: 119319005).I only want those ids which contain RG and MI only.
September 17, 2014 at 7:25 am
atultiwari.eng06 (9/17/2014)
Tablename Consumer:ID Resource
119319004 RG
119319004 MI
119319005 RG
119319005 MI
119319005 BT
119319006 RG
I want id whose Resource is only RG and MI that's(119319004)
Output
119319004
Please reply..
Could you do something like:
with cte1 as (select id, count(*) from mtable where mtable.resource in ('rg', 'mi')
group by id having count(*) = 2)
select id
from mtable
inner join cte1 on mtable.id = cte1.id ;
I know this may or may not work for you as written but maybe it will give you an idea.
September 17, 2014 at 7:35 am
Ah, I see. How about:-
SELECT C1.ID, C1.[Resource]
FROM dbo.[Consumer] C1
WHERE NOT EXISTS
(SELECT ID
FROM dbo.[Consumer] C2
WHERE C2.[Resource] NOT IN ('MI','RG')
AND C2.ID = C1.ID)
AND EXISTS
(SELECT ID
FROM dbo.[Consumer] C3
WHERE C3.[Resource] = 'RG'
AND C3.ID = C1.ID)
AND EXISTS
(SELECT ID
FROM dbo.[Consumer] C4
WHERE C4.[Resource] = 'MI'
AND C4.ID = C1.ID)
September 17, 2014 at 8:00 am
select c.ID,count(*) [Cnt]
from dbo.Consumer c
where c.Resource in ('RG','MI') and c.ID=119319004
group by c.ID
having count(*)=2
Igor Micev,My blog: www.igormicev.com
September 17, 2014 at 8:52 am
Please see my next reply.
September 17, 2014 at 8:53 am
You should read the discussion on this article. Of course, you must change the conditions a little bit for "C".
September 17, 2014 at 9:01 am
Luis Cazares (9/17/2014)
You should read the discussion on this article. Of course, you must change the conditions a little bit for "C".
Thank you Luis - for the clue.
Based on the article Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)[/url]
SELECT
ID
FROM dbo.Consumer
WHERE Resource IN ('RG','MI')
GROUP BY ID
HAVING COUNT(DISTINCT Resource) =2
EXCEPT
-- Ignore all Resource except RG and MI
SELECT ID
FROM dbo.Consumer
WHERE Resource NOT IN ('RG','MI');
September 17, 2014 at 10:28 am
Thanks bro u nailed it
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply