SELECT STATE_ID
FROM GROUP_STATE
STATE_ID
----------------
10
15
16
17
18
19
20
SELECT GROUP_ID
FROM GROUP
WHERE GROUP_ID NOT IN (SELECT DISTINCT GROUP_ID FROM GROUP_STATE)
GROUP_ID
---------------
1
2
16
5
I need a query that would look like:
GROUP_ID STATE_ID
--------------- ----------------
1 10
1 15
1 16
1 17
1 18
1 19
1 20
2 10
2 15
2 16
2 17
2 18
2 19
2 20
ETC....
try this
create table #t(i int)
create table #t1(i int)
insert into #t values(10)
insert into #t values(15)
insert into #t values(16)
insert into #t values(17)
insert into #t values(18)
insert into #t values(19)
insert into #t values(20)
insert into #t1 values(1)
insert into #t1 values(2)
insert into #t1 values(16)
insert into #t1 values(5)
select t1.i,t.i from #t1 t1 cross join #t t
regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"jsmith" <jsmi...@gmail.com> wrote in message
news:1181769003.7...@q19g2000prn.googlegroups.com...
Do not use reserved words for data element names.
The DISTINCT is not necessary, IN does an implicit DISTINCT anyway.
I'm suprised you didn't know that.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:1181770221.2...@z28g2000prd.googlegroups.com...
Thanks a lot