Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Need urgent help on a QUERY

5 views
Skip to first unread message

jsmith

unread,
Jun 13, 2007, 5:10:03 PM6/13/07
to
Given:

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....

Vt

unread,
Jun 13, 2007, 5:27:57 PM6/13/07
to

HI

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...

--CELKO--

unread,
Jun 13, 2007, 5:30:21 PM6/13/07
to
SELECT GS.state_id, G.group_id
FROM ( SELECT state_id FROM GroupStates AS GS
CROSS JOIN
SELECT group_id FROM Groups
WHERE group_id
NOT IN (SELECT DISTINCT group_id FROM GroupStates)
AS G);

Do not use reserved words for data element names.

Tony Rogerson

unread,
Jun 14, 2007, 12:44:24 AM6/14/07
to
> NOT IN (SELECT DISTINCT group_id FROM GroupStates)

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...

jsmith

unread,
Jun 14, 2007, 10:14:46 AM6/14/07
to
On Jun 14, 12:44 am, "Tony Rogerson" <tonyroger...@torver.net> wrote:
> > NOT IN (SELECT DISTINCT group_id FROM GroupStates)
>
> The DISTINCT is not necessary, IN does an implicit DISTINCT anyway.
>
> I'm suprised you didn't know that.
>
> --
> Tony Rogerson, SQL Server MVPhttp://sqlblogcasts.com/blogs/tonyrogerson

> [Ramblings from the field from a SQL consultant]http://sqlserverfaq.com
> [UK SQL User Community]
>
> "--CELKO--" <jcelko...@earthlink.net> wrote in message

>
> news:1181770221.2...@z28g2000prd.googlegroups.com...
>
> > SELECT GS.state_id, G.group_id
> > FROM ( SELECT state_id FROM GroupStates AS GS
> > CROSS JOIN
> > SELECT group_id FROM Groups
> > WHERE group_id
> > NOT IN (SELECT DISTINCT group_id FROM GroupStates)
> > AS G);
>
> > Do not use reserved words for data element names.

Thanks a lot

0 new messages