List:General Discussion« Previous MessageNext Message »
From:Lewis Smith Date:October 24 2002 11:21am
Subject:outer join
View as plain text  
I am the process of converting my companies applications from MS SQL to mySQL.  It is all
going OK, however I am having trouble with the following 

SELECT Member.memberid, Member.mcname, Member.msname, UAGroup.groupname, UAGroup.groupid,
Member.buid FROM UAGroup 
INNER JOIN UAMemberOfGroup ON UAGroup.groupid = UAMemberOfGroup.groupid 
RIGHT OUTER JOIN Member ON UAMemberOfGroup.memberid = Member.memberid 
WHERE Member.mpassword IS NOT NULL AND Member.musername IS NOT NULL

The idea is to bring back all members and any groups they happen to belong to.

In MS SQL the query brings back all members and has NULL entries for groupid if they are
not in a group.

In mySQL the query brings back all members, each of whom has an entry for each groupid.

Any one have any idea how I can make the query perform as it does in MS SQL?

Thanks in advance,

Lewis Smith
@ Work

outer joinLewis Smith24 Oct