a SQL Question???

[markshen2004]

I have three tables(Groups,Members and GroupMember).Groups and Members are data table and groupMember have the relation between them.

That structure and data are here:

Members

member_id member_name
1 m1
2 m2
3 m3
4 m4

groups

group_id group_name
1 G1
2 G2
3 G3

GroupMember

member_id Group_id
1 1
2 1

I want to get the member names that are not in group G2. I use the SQL statement like this

SELECT M.member_name
FROM Members
INNER JOIN GroupMember GM ON M.member_id=GM.member_id
WHERE GM.group_id <> 2

but I only get

m1
m2

but I want to the result is like the list here

m1
m2
m3
m4

because the GroupMember doesn't have the date about m3, m4 so they can not display.Please let me know how to write the SQL statement to to get the member names that are not in group G2 and include m3 and m4 in the result.

Thanks a lot

 

[PHV]

Replace this:
INNER JOIN
By this:
LEFT JOIN
You may also have to replace this:
WHERE GM.group_id <> 2
By this:
WHERE (GM.group_id <> 2 Or GM.group_id Is Null)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244