Query help needed

[Nick]

I have 2 dbs

a)Relation
User | Group
4 | 2
4 | 3
5 | 4
5 | 5
4 | 4

b)Group
ID | Name
2 | Grp1
3 | Grp2
4 | Grp3
5 | Grp4
What i would like to do is get a result set of
Relation.Group, Group.Name
5, Grp4

Info at hand before query is user=4, so what i am looking for is all
groups that are not subscribed by user 4.

So far i tried:
$Query = 'SELECT DISTINCT relation.group, group.Name
FROM `group`, `relation` WHERE relation.user !=
\''.$userid.'\' AND relation.group = group.id';

This just gives me all groups that do not have user=4.

How do i modify it.
Thanks in advance.

[Bill Karwin]

Nick wrote:

What i would like to do is get a result set of
Relation.Group, Group.Name
5, Grp4

Here's an example that uses subqueries (requires MySQL 4.1 or later):

SELECT DISTINCT G.ID, G.Name
FROM group G INNER JOIN relation R ON G.ID = R.Group
WHERE G.ID NOT IN
(SELECT R2.Group FROM relation R2 WHERE R2.User = $user)

Here's an example using outer joins (works with MySQL 4.0 or 3.2x):

SELECT DISTINCT G.ID, G.Name
FROM group G LEFT OUTER JOIN relation R
ON (G.ID = R.Group AND R.User = $user)
WHERE R.User IS NULL

Caveat: these queries are not tested.

Regards,
Bill K.

[Nick]

Thanks,
That worked great.
On Fri, 24 Sep 2004 16:23:33 -0700, Bill Karwin <bi**@karwin.com>
wrote:

Nick wrote:

What i would like to do is get a result set of
Relation.Group, Group.Name
5, Grp4

Here's an example that uses subqueries (requires MySQL 4.1 or later):

SELECT DISTINCT G.ID, G.Name
FROM group G INNER JOIN relation R ON G.ID = R.Group
WHERE G.ID NOT IN
(SELECT R2.Group FROM relation R2 WHERE R2.User = $user)

Here's an example using outer joins (works with MySQL 4.0 or 3.2x):

SELECT DISTINCT G.ID, G.Name
FROM group G LEFT OUTER JOIN relation R
ON (G.ID = R.Group AND R.User = $user)
WHERE R.User IS NULL

Caveat: these queries are not tested.

Regards,
Bill K.