Selecting entries which are NOT in lookup

[Rekcor]

For my CMS, I've got two lookuptables

* pagereadlookup

+-----------------+ 
| userId | pageID |
+-----------------+

* pagewritelookup

+-----------------+ 
| userId | pageID |
+-----------------+

and a table with page properties

* page

+-----------------+ 
| ID | name | URL |
+-----------------+

What I want is, given an user with userId=x, to select all pages which this user may both NOT read and NOT write.

I've tried a lot but I can't figure this out. Who can help me?

 

[jokobe]

Maybe it works like this:

SELECT pages.user_id, pages.name
FROM pages
where
user_id not in ( select user_id from pageread )
and user_id not in (select user_id from pagewrite)

Have tried that in an ACCESS-Database for four users and it
is working. Maybe the syntax for MYSQL is slightly different.

 

[Rekcor]

Thanks, but this seems only to be working in MySQL versions 4.1 and up (and my provider is running 4.0.23 <sigh>)

 

[r937]

select P.ID  
     , P.name
     , P.url
  from page as P
left outer
  join pagereadlookup as PR
    on P.ID = PR.pageID
   and PR.userId = [i]userid[/i]
left outer
  join pagewritelookup as PW
    on P.ID = PW.pageID
   and PW.userId = [i]userid[/i]
 where PR.userID is null
   and PW.userID is null

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)