Page 1 of 1

JOINING 3 TABLE syntax question [SOLVED]

Posted: Tue Jan 24, 2006 10:56 am
by kendall
Hey,

I have tree tables

Code: Select all

cms_userprivileges
PID | UserID | TypeID
1|123|2

cms_users
|UserID|UserName|
123|admin
234|kendall
345|somoneelse

cms_types
TypeID|Name
1|admin
2|content provider
3|publisher
Im trying to list all the cms_users while showing the name of the type of user that they are

Code: Select all

SELECT
      cuser.UserID,
      cuser.FirstName,
      cuser.LastName,
      cuser.UserName,
      cuser.UserEmail,
      puser.TypeID,
      ctype.TypeName,
	  cuser.`Status`
FROM cms_userprivileges AS puser, cms_users
RIGHT JOIN cms_users AS cuser ON cuser.UserID = puser.UserID AND puser.UserID IS NULL
LEFT JOIN cms_usertypes AS ctype ON ctype.TypeID = puser.TypeID AND puser.TypeID IS NULL
The above codes lists out the users
and gets the admin type
but list the admin type as 2 for users who arent in the userprivileges table when it should be null...ctype.TypeName is returning null

can't really get a handle on what it is i need to check for to return null in the rows that need to have it

Kendall

Posted: Tue Jan 24, 2006 12:38 pm
by feyd

Code: Select all

SELECT
	cuser.UserID,
	cuser.FirstName,
	cuser.LastName,
	cuser.UserName,
	cuser.UserEmail,
	puser.TypeID,
	ctype.TypeName,
	cuser.`Status`
FROM
	cms_users AS cuser
LEFT JOIN
	cms_userprivileges AS puser
	ON
		cuser.UserID = puser.UserID
LEFT JOIN
	cms_usertypes AS ctype
	ON
		ctype.TypeID = puser.TypeID
I think... :?:

Posted: Tue Jan 24, 2006 1:57 pm
by raghavan20
I have rarely seen people using right joins... I think in most situations, normal joins and left joins would suffice. I know right join is about changing the order of assignment of dependency but left join has more clarity, ofcouse I used to think things better from left to right....