Assistance With Tricky SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Assistance With Tricky SQL

Author

Topic

AlphaMeasure
Starting Member

1 Post

Posted - 2007-05-09 : 21:36:54

I'm trying to write what I intially thought would be an easy SQL statement. I was hoping to get some assistance from some of the pros in this group.

Here is my problem (this is a fictious structure).

Table I
ID
ColumnI
ColumnII

Sample Data:
ID State Country
1 CO US
1 CA US
3 GR FR
2 CA US
1 MA US
5 PO GE
6 CA US
2 MA US
8 XO GE
2 CO US

I want to write some SQL that will select IDs that are in the US and in ALL the states (CA, CO, MA). I would expect ID's 1 and 2 to be returned.

I tried writing the statement using an OR, but that doesn't enforce that an ID must be in all the states to be returned. As well as, using AND doesn't work either. I think the answer lies in using IN coupled with embedded Select statements.

The actual data is more complicated, using joins, than the example above, but I think if I have the SQL above that I can fill in the blanks.

Thanks In Advance!
Josh

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-09 : 22:55:06

[code]
select
a.ID
from
(
Select
aa.ID,
State_Count = count(distinct aa.State)
from
Mytable aa
where
aa.Country = 'US'
group by
aa.ID
) a
join
(
select
State_Count = count(distinct bb.State)
from
MyTable bb
where
bb.Country = 'US'
) b
on a.State_Count = b.State_Count[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources