Question contructing a MySQL query

Forum Moderators: open

Message Too Old, No Replies

Question contructing a MySQL query

smithaa02

2:41 pm on Sep 14, 2006 (gmt 0)

Say I have the following tables:

Table: People
Fields: ID, Name

Table: Memberships
Fields ID, Name, Group

How I would construct a SQL statement to get all the people who do not have memberships to any groups at all?

zomega42

3:30 pm on Sep 14, 2006 (gmt 0)

If you have a recent version of Mysql (I think v4 will do it, it has to support subqueries), you could use

select * from people
where id not in (select id from memberships)

smithaa02

3:39 pm on Sep 14, 2006 (gmt 0)

Unfortinitly, I'm using an older version which does not support subqueries.

If anybody is curious, I did figure out how to do this... Do a left join starting with the people table and add a where clause that checks if name 'is Null'. That did the trick.

coopster

1:23 pm on Sep 15, 2006 (gmt 0)

That's exactly how to do so when Subqueries are not an option.
Rewriting Subqueries as Joins for Earlier MySQL Versions [dev.mysql.com]