JOINs problem

[burhankhan]

Hi:

 

I have two tables as:

 

Members
----------------------------
MEMBER_ID | NAME
-----------------------------
      1            abc
      2            kdjf
      3            kek

GROUP
-------------------------
MEMBER_ID    |  GROUP_ID
-------------------------
      3                   1

 

 

Now i want to fetch records from first table, where members.member_id != groups.members_id

 

I use many types of quires and it is working:

 

SELECT * FROM `members` RIGHT OUTER JOIN `member-group` ON `member-group`.memberid != members.memberid

 

SELECT * FROM `members` WHERE `id` != (SELECT `memberid` FROM `groups`)

 

Also left join working...

 

 

But when second table "GROUP" is empty, then no query is working. It fetch ZERO records.

 

What is its solution?

 

 

 

[fenway]

Ugh... != is evil.  If you want to find non-matching rows, use a LEFT JOIN with the FK constraint in the ON clause and use IS NULL in the where clause.

[burhankhan]

I couldn't understand what you said.......

 

I use left join as:

 

SELECT * FROM `members` LEFT JOIN `member-group` ON `member-group`.memberid IS NULL

 

But still not working...

[bubblegum.anarchy]

SELECT * FROM members
     LEFT JOIN member-group ON members.member_id = member-group.member_id 
WHERE member-group.member_id IS NULL

[fenway]

SELECT * FROM members
     LEFT JOIN member-group ON members.member_id = member-group.member_id 
WHERE member-group.member_id IS NULL

Yeah, I guess I should have spelled that out... my bad.