March 9, 2009 at 7:34 pm
Hi,
My table structure is like this.
TABLE: TEST
ID countries
100 IN, AU, GB, FR
101 GB, FR, IN
102 IN, AU
103 AU, GB
104 GB, FR, IN, AU
I want to extract the ids that have "IN" AND "AU" only.
But when I run this query, it brings all ids that have "IN" OR "AU"
SELECT ID FROM TEST
WHERE COUNTRIES IN ('IN', 'AU')
The result is
ID
100
101
102
103
104
The result I expect to see is
ID
100
102
104
only.
Please help.
Thank you in advance.
March 9, 2009 at 8:14 pm
Well I am going to assume that you table structure description is incorrect, and the your query attempt reflects that actual structure of your data.
If so, then this should do it:
SELECT ID
FROM TEST
WHERE ID IN (Select t2.ID From TEST t2
Where t2.COUNTRIES='AU')
AND COUNTRIES = 'IN'
edit: typo
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 9, 2009 at 8:46 pm
jmucherla (3/9/2009)
Hi,My table structure is like this.
TABLE: TEST
ID countries
100 IN, AU, GB, FR
101 GB, FR, IN
102 IN, AU
103 AU, GB
104 GB, FR, IN, AU
I want to extract the ids that have "IN" AND "AU" only.
But when I run this query, it brings all ids that have "IN" OR "AU"
SELECT ID FROM TEST
WHERE COUNTRIES IN ('IN', 'AU')
Barry is correct... that query won't give you any results against the data table you've listed. Which is correct? The table you have listed or the query?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 9, 2009 at 9:01 pm
My table structure is actually like this.
TABLE: TEST
ID Countries
100 IN
100 AU
100 GB
100 FR
101 GB
101 GB
101 FR
101 IN
102 IN
102 AU
103 AU
103 GB
104 GB
101 FR
101 IN
101 AU
March 9, 2009 at 9:11 pm
If you want to exclude those ID's that have other countries besides AU and IN then use this:
SELECT ID
FROM TEST
WHERE ID IN (Select t2.ID From TEST t2 Where t2.COUNTRIES='AU')
AND COUNTRIES = 'IN'
AND ID NOT IN (Select t3.ID From TEST t3 Where t3.COUNTRIES not in ('AU', 'IN'))
Bevan
March 9, 2009 at 9:15 pm
Or this works as well:
Select t3.ID From #TEST t3 Where t3.COUNTRIES in ('AU', 'IN')
except
Select t3.ID From #TEST t3 Where t3.COUNTRIES not in ('AU', 'IN')
Looks slower based on execution plan but I'm not sure what it would do over big recordsets.
Bevan
March 9, 2009 at 9:30 pm
or slightly fast if there is a clustered primary key on ID and countries...
Bevan
March 9, 2009 at 10:39 pm
Bevan keighley (3/9/2009)
Or this works as well:
Select t3.ID From #TEST t3 Where t3.COUNTRIES in ('AU', 'IN')except
Select t3.ID From #TEST t3 Where t3.COUNTRIES not in ('AU', 'IN')
Looks slower based on execution plan but I'm not sure what it would do over big recordsets.
Bevan
Try it against the data posted... won't return what the op requested.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 9, 2009 at 10:44 pm
jmucherla (3/9/2009)
My table structure is actually like this.TABLE: TEST
ID Countries
100 IN
100 AU
100 GB
100 FR
101 GB
101 GB
101 FR
101 IN
102 IN
102 AU
103 AU
103 GB
104 GB
101 FR
101 IN
101 AU
In the future, if you could make that look like the following, it would be a huge help...
CREATE TABLE #Test
(ID INT, Countries CHAR(2))
INSERT INTO #Test
(ID, Countries)
SELECT '100','IN' UNION ALL
SELECT '100','AU' UNION ALL
SELECT '100','GB' UNION ALL
SELECT '100','FR' UNION ALL
SELECT '101','GB' UNION ALL
SELECT '101','GB' UNION ALL
SELECT '101','FR' UNION ALL
SELECT '101','IN' UNION ALL
SELECT '102','IN' UNION ALL
SELECT '102','AU' UNION ALL
SELECT '103','AU' UNION ALL
SELECT '103','GB' UNION ALL
SELECT '104','GB' UNION ALL
SELECT '104','FR' UNION ALL
SELECT '104','IN' UNION ALL
SELECT '104','AU'
See the link in my signature for how to do that easily... you'll get better answers quicker.
Based on that, Barry's code will do the trick.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 9, 2009 at 10:49 pm
... And, if the combination of ID and Countries forms unique pairs, here's another way that's super easy to expand to additional countries...
SELECT ID
FROM #Test
WHERE Countries IN ('IN','AU')
GROUP BY ID
HAVING COUNT(*) = 2
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2009 at 1:46 am
You may need to use this in case duplicates are allowed
SELECT ID
FROM #Test
WHERE Countries IN ('IN','AU')
GROUP BY ID
HAVING COUNT(DISTINCT Countries) = 2
Failing to plan is Planning to fail
March 10, 2009 at 1:45 pm
Jeff Moden (3/9/2009)
Bevan keighley (3/9/2009)
Or this works as well:
Select t3.ID From #TEST t3 Where t3.COUNTRIES in ('AU', 'IN')except
Select t3.ID From #TEST t3 Where t3.COUNTRIES not in ('AU', 'IN')
Looks slower based on execution plan but I'm not sure what it would do over big recordsets.
Bevan
Try it against the data posted... won't return what the op requested.
Hmmmmm, I think this is a case of unclear requirements! (seen that before!)
I want to extract the ids that have "IN" AND "AU" only.
I read this as: "IN" AND "AU" AND nothing else. It would be good to know the OP's actual intentions here.
Bevan
March 10, 2009 at 1:59 pm
Yeah... I agree... the written requirements weren't that clear. The desired return posted along with the attempted code and the existing data was the key.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2009 at 2:14 pm
Here is another possible solution:
CREATE TABLE #Test
(ID INT, Countries CHAR(2));
INSERT INTO #Test
(ID, Countries)
SELECT '100','IN' UNION ALL
SELECT '100','AU' UNION ALL
SELECT '100','GB' UNION ALL
SELECT '100','FR' UNION ALL
SELECT '101','GB' UNION ALL
SELECT '101','GB' UNION ALL
SELECT '101','FR' UNION ALL
SELECT '101','IN' UNION ALL
SELECT '102','IN' UNION ALL
SELECT '102','AU' UNION ALL
SELECT '103','AU' UNION ALL
SELECT '103','GB' UNION ALL
SELECT '104','GB' UNION ALL
SELECT '104','FR' UNION ALL
SELECT '104','IN' UNION ALL
SELECT '104','AU';
with ID_Countries (
ID,
CountryIDs
) as (
select
ID,
stuff((select ',' + Countries from #Test T2 where T2.ID = T1.ID for xml path ('')),1,1,'')
from
#Test T1
group by
ID
)
select
ID
from
ID_Countries
where
CountryIDs like '%IN%' and
CountryIDs like '%AU%'
;
drop table #Test;
March 10, 2009 at 3:22 pm
And, just for S&G's here is another alternative:
CREATE TABLE #Test
(ID INT, Countries CHAR(2));
INSERT INTO #Test
(ID, Countries)
SELECT '100','IN' UNION ALL
SELECT '100','AU' UNION ALL
SELECT '100','GB' UNION ALL
SELECT '100','FR' UNION ALL
SELECT '101','GB' UNION ALL
SELECT '101','GB' UNION ALL
SELECT '101','FR' UNION ALL
SELECT '101','IN' UNION ALL
SELECT '102','IN' UNION ALL
SELECT '102','AU' UNION ALL
SELECT '103','AU' UNION ALL
SELECT '103','GB' UNION ALL
SELECT '104','GB' UNION ALL
SELECT '104','FR' UNION ALL
SELECT '104','IN' UNION ALL
SELECT '104','AU';
declare @TestVals varchar(32);
set @TestVals = ',' + 'IN,AU' +',';
with
a1 as (select 1 as N union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1),
a2 as (select
1 as N
from
a1 as a
cross join a1 as b),
a3 as (select
1 as N
from
a2 as a
cross join a2 as b),
a4 as (select
1 as N
from
a3 as a
cross join a2 as b),
Tally as (select
row_number() over (order by N) as N
from
a4),
CheckCountries(
Countries
) as (
SELECT
SUBSTRING(@TestVals,N + 1,CHARINDEX(',',@TestVals,N + 1) - N - 1)
FROM
Tally
WHERE
N < LEN(@TestVals)
AND SUBSTRING(@TestVals,N,1) = ',' --Notice how we find the comma
),
UniqueCountries (
CountryCnt
) as (
select
count(distinct Countries)
from
CheckCountries
)
select
ID
from
#Test t
inner join CheckCountries cc
on (t.Countries = cc.Countries)
cross join UniqueCountries uc
group by
ID,
uc.CountryCnt
having
count(distinct t.Countries) = uc.CountryCnt
drop table #Test;
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply