mysql query

[chings]

i have two tables currency

1. the table contains client infomation

table - member
id name
1 john
2 lee
3 george

2 another table contains the information which group the client belongs
table - group
group_id id
2 1 (john)
2 2 (lee)

i want to find out which id doesn't belong to any group

so i try
SELECT name.id FROM group,member WHERE group.id != member.id

finally
the result

id
3
3

so what's the problem of my query, thanks

select

[bpat1434]

1.) Why are you using a table to link the groups and the names, why not just add a column "group_id" to your member table?

2.) Your query should most probably be:

SELECT a.id 
  FROM member AS a
  LEFT JOIN group AS b
    ON a.id=b.id
  WHERE b.id IS NULL

Read the user-notes in the mySQL manual here

[chings]

i can't do that , since one member can belong to several group

[bpat1434]

Okay, so use my second suggestion....

[chings]

thanks it works , but actually i have two more criteria.

If i put the criterias after the WHERE , it becomes zero record
if i put the criterias after the ON , same result

[bpat1434]

Well, if you put criteria in the ON statement, the tables will use all those fields to join information, so it's best to use just one per table duo.

As for the where, it shouldn't do that. What are you trying to add?