Elegant solution for SELECT clause... 2

[karnaf]

I'm trying to avoid multiple calls to SQL and saving info in arrays... trying to find an elegant solution for a query...


I have two tables in my DB for controling which pages each user can reach-

user_access (user_id, page_id)
user_pages (page_id, page_name)

now I want to create a SELECT query which will give me all user_pages and for a specific user, put '1' if he can access this page and '0' if he cant in another column.

for example:
[tt]
user_access
user_id | page_id
123 | 2
262 | 2
262 | 4
262 | 5

user_pages
page_id | page_name
1 | one.html
2 | two.html
3 | three.html
4 | four.html
5 | five.html


then I want my result for SELECT for user_id=262 to look like
page_id | page_name | allowed
1 | one.html | 0
2 | two.html | 1
3 | three.html | 0
4 | four.html | 1
5 | five.html | 1
[/tt]

any ideas for a single SELECT query to do that? something with IF or CASE i guess, no?

THANKS!!!

(MySQL 3.23.56)

 

[BNPMike]

If you do an outer join from user_pages to user_access, all the pages the user has access to will have a value like 262 whereas all the ones without access will be null. Either use this to create a 0/1 field or just adjust your code to respond to null/not-null.

 

[karnaf]

Thanks, but it's not working.

For some reason I can't use just OUTER JOIN, I have to use LEFT OUTER JOIN or RIGHT OUTER JOIN and then its the same as doing

"SELECT up.* , ua.user_id FROM user_access AS ua, user_pages AS up WHERE user_id = 262 AND ua.page_id = up.page_id;"

could it be my MySQL version???
:-(

When I try to do

"SELECT up.*, ua.user_id FROM user_pages AS ap OUTER JOIN user_access AS ua USING (page_id) WHERE user_id = 262"

I get:
"You have an error in your SQL syntax near 'OUTER JOIN ...."

:-( :-(

 

[vanekl]

try this,

SELECT p.*, IF(a.user_id is null, 0, 1) AS allowed
FROM user_pages p LEFT OUTER JOIN user_access a ON (a.page_id=p.page_id)
WHERE user_id = 262

 

[BNPMike]

vanekl
I've given you a star. People just don't give enough stars on this channel, you know what I mean?.

 

[karnaf]

Star light, star bright still not working...

Using vanekl's suggestion, I still only get the 3 lines of the pages the user has access to and not all pages....

meaning, what I get is

[tt]page_id | page_name | allowed
2 | two.html | 1
4 | four.html | 1
5 | five.html | 1[/tt]

I guess I will have to use two queries and an array to keep the info.... :-( :-(

thank you all

 

[vanekl]

SELECT p.*, IF(a.user_id is null, 0, 1) AS allowed
FROM user_pages p LEFT OUTER JOIN user_access a ON a.page_id=p.page_id
AND user_id = 262

 

[karnaf]

YEEPEE!!!



thank you so very much...

 

[tshot]

Hi karnaf
if yr followin code u LEFT and importnat key word LEFT just before OUTER.

[core]"SELECT up.*, ua.user_id FROM user_pages AS ap OUTER JOIN user_access AS ua USING (page_id) WHERE user_id = 262"[/code]. Still yr requd solution is as u say YEEPEE!!!




----------------
ur feedback is a very welcome desire

 

[karnaf]

Well, as I have said before, whenever I use OUTER JOIN without LEFT or RIGHT I get

"You have an error in your SQL syntax near 'OUTER JOIN user_access AS...."

I guess it has to do with the MySQL version I'm using, maybe it just doesn't support 'OUTER JOIN'. Im using version 3.26.56

but vanekl's last suggestion works wonders

 

[tshot]

ur version of MySql supports OUTER JOIN but not without its key words LEFT or RIGHT

Even the latest version 4.1 also requires these keywords




----------------
ur feedback is a very welcome desire