select Name
from Table
where GroupID in (1,7)
group by Name
having count(GroupID) = 2;
This is brilliant, wish I'd thought of it :)
Dave.
-----Original Message-----
From: Nic Wise [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 4 November 1999 16:11
To: Multiple recipients of list database
Subject: [DUG-DB]: SQL from hell.... (was something else...)
>From my flatmate.... definatly has more brains than me :)
N
Damon Maria wrote:
>
> Nic Wise wrote:
> > There is a table:
> >
> > name GroupID
> > nic 1
> > nic 4
> > nic 7
> > damon 1
> > damon 2
> > damon 6
> > damon 7
> > nod 3
> > nod 8
> > etc
> >
> > how does one get a list (using SQL) of all people in BOTH
> > groups 1 and 7
> > (for example), but not in 1 OR 7
>
> I presume, you mean "not 1 OR 7 only" with that last statement.
>
> Most Generic and maybe fastest:
>
> select Name
> from Table
> where GroupID in (1,7)
> group by Name
> having count(GroupID) = 2;
>
> or (if your DB supports Intersect like Oracle)
>
> select Name
> from Table
> where GroupID = 1
> intersect
> select Name
> from Table
> where GroupID = 7
>
> or
>
> select Name
> from Table
> where
> GroupID = 1
> and Name in
> (select Name
> from Table
> where GroupID = 7);
>
> or
>
> select Name
> from Table t
> where
> GroupID = 1
> and exists
> (select Name
> from Table
> where
> Name = t.Name
> GroupID = 7);
>
> D.
--
Nic Wise - Inprise New Zealand / Brocker Technologies Web Monkey.
mob:+64.21.676.418 - wk:+64.9.481.9999 x9753 - wk-em:[EMAIL PROTECTED]
hm: +64.9.277.5309 - hm-em:[EMAIL PROTECTED]
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
