The Database Forum

#1 (permalink)

HI,

IM attempting to join 3 tables but having problems

my tables are shown below

_intranetUsers

userID
foreName
surName

_intranetGroups

groupID
groupName

_intranetUsersGroupsLookup (primary table)

groupID
userID

I wish to select _intranetUsersGroupsLookup but look up , forename/surname from _intranetUsers and groupName from _intranetGroups.

However, i wish ALSO to show non matches in _intranetUsers and non matches in intranetGroups

My SQL is below :

SELECT a.groupID, a.userID, b.firstName, b.lastName, c.groupName FROM
_intranetUsersGroupsLookup a
RIGHT JOIN _intranetUsers b ON a.userID = b.userID
LEFT JOIN _intranetGroups c ON a.groupID = c.groupID
ORDER BY a.groupID

Problem is although the non matches for _intranetUsers is shown, When i define a RIGHT JOIN a to c instead of LEFT JOIN a to c i return NO records.
(there are users and groups NOT defined in _intranetUsersGroupsLookup that i wish to output)

Can anyone please help as this is well frustrating me.

cheers

Steve

__________________

Please login or register to view this content. Registration is FREE