Right join query?

[samtwilliams]

Hi All,

 

I have a problem that i can't solve with a right join. I have a members access system which comprises of two tables PageLevels and UserPages. My PageLevels consists of all possible pages in my site and the other lists the ones a particular user has access too. I am now creating my user editing page and need to list ALL pages in my site and apply a checkbox for those the member has access to. The problem comes when I join my tables and apply my WHERE clause (WHERE UserPages.uid = 1) which causes it to show only those pages a member has access to rather than all.

 

My tables:

PageLevels:

level|description|name

PR001|Home Page|home

PR002|contact us|contact

PR003|help page|help

 

UserPages:

page|uid

PR001|1

PR002|1

 

My Query

SELECT PageLevels.*, UserPages.uid FROM PageLevels

RIGHT JOIN UserPages ON UserPages.page = PageLevels.level WHERE UserPages.uid = 1

 

Now if i run my query on this i expect it to show result set like

 

PR001|1

PR002|NULL

PR003|1

 

I know it has something to do with running the WHERE on the JOINED table but I am now at a loss on how to get this to work.

 

Anyone have any ideas?

 

Thanks

Sam

[kickstart]

Hi

 

The WHERE clause filters the rows after the join, where you need to filter the data before the join. Also think you need a LEFT OUTER JOIN.

 

Try

 

SELECT PageLevels.*, UserPages.uid 
FROM PageLevels
LEFT OUTER JOIN UserPages 
ON UserPages.page = PageLevels.level 
AND UserPages.uid = 1

 

Or in easier to understand (but should be less efficient)

 

SELECT PageLevels.*, UserPagesSub.uid 
FROM PageLevels
LEFT OUTER JOIN (SELECT * FROM UserPages WHERE AND UserPages.uid = 1) UserPagesSub
ON UserPagesSub.page = PageLevels.level 

 

All the best

 

Keith

[samtwilliams]

Hi Keith,

 

Thanks for your reply, have had a tinker but can not get rid of this error.

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND UserPages.uid = 1) UserPagesSub ON UserPagesSub.page = PageLevels.level LI' at line 3

[samtwilliams]

Sorted, additional AND.

 

Thanks Keith.

 

Sam

[kickstart]

Sorted, additional AND.

 

. Sorry.

 

All the best

 

Keith