JOINING 3 TABLE syntax question [SOLVED]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

JOINING 3 TABLE syntax question [SOLVED]

Post 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
Last edited by kendall on Tue Jan 24, 2006 2:15 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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... :?:
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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....
Post Reply