Join with NOT

saltwater

Hi,

I have 3 tables

table1 (cms_content):
id (Primary Key)
content

table2 (cms_allowed):
id (Primary Key)
pageid
userid

table3 (cms_admin):
id (Primary Key)
username
password
email

My session contains a variable $userid witch contains cms_admin.id of the current logged in user

Now, I want a recordset that contains all records from cms_content EXCEPT for the ones witch the below statement is true
cms_content.id=cms_allowed.pageid AND cms_allowed.userid='$userid'

What query should I use? I've tried with ...WHERE NOT (cms_content.id=cms_allowed.pageid AND cms_allowed.userid='$userid') but that didn't work as it is supposed to.
Please help a desperate programmer 🙂

amcgrath

have you checked out the EXCEPT statment by chance?

saltwater

I forgot to mention I use MySQL...
Does MySQL support the EXCEPT statement? Because i can't find any information about it...

[deleted]

select * from cms_content,cms_allowed where cms_content.id!=cms_allowed.pageid and cms_allowed.userid!='$userid';

saltwater

I maybe didn't explain verry well what I am after, sorry if so 😉

When I try Karl's query, I get all pages that the userid in cms_allowed is not $userid

I want to get all pages in cms_content except for the one's that have their id in a record with their id=pageid and $userid=userid

Hope this covers it better 😉
Thanks already for your efforts!

amcgrath

Honestly, I don't know. I use postgres which does support the EXCEPT statement... you could try something like select * from table_a where a_id not in (select a_id from table b)....

saltwater

hmmz
tried that, it gave me an error 🙂

Anon

So do you want to get all of the pages that the user is allowed to edit or do you want to get all of the pages that the user is NOT allowed to edit?

Here's queries for both cases. No idea if this is what you have in mind, (and now that I look at it a little more I don't think the 'Not allowed to edit' query will work as well as it should) but hopefully you have enough examples now that you'll be able to handle it on your own. Just remember that the != is a negation operator and will have the same effect as the 'NOT' keyword will have.

When you're doing joins like this, also keep in mind that the order you put things in matters. Here, we're limiting by userid first, and then we're figuring out which pageID's match/don't match that userid.

Allowed to edit:
select cms_content.* from cms_content,cms_allowed where cms_allowed.userid='$userid' and cms_content.id=cms_allowed.pageid;

Not allowed to edit:
select cms_content.* from cms_content,cms_allowed where cms_allowed.userid='$userid' and cms_content.id!=cms_allowed.pageid;

Try those and let me know how they work. Maybe I'm just totally missing you here... and wow, it's early. Must go find caffeine.

(P.S. - Allen, he uses MySQL, which doesn't (yet) support subqueries. Yeah, yeah yeah, I know.)

saltwater

Thanks very much Karl for your effort but ... this still doesn't hold the anwser 🙂

When I execute:

Allowed to edit:
select cms_content.* from cms_content,cms_allowed where cms_allowed.userid='$userid' and cms_content.id=cms_allowed.pageid;

I get all pages I am not allowed to edit

And when I execute:

select cms_content.* from cms_content,cms_allowed where cms_allowed.userid='$userid' and cms_content.id!=cms_allowed.pageid;

I get all pages I am allowed to edit and some pages I am not allowed to, witch is logical if you look at the recordset I receive:

cms_content.id cms_allowed.userid cms_allowed.pageid

1 1 2
3 1 2
4 1 2
8 1 2
9 1 2
10 1 2
12 1 2
13 1 2
14 1 2
15 1 2
16 1 2
17 1 2
18 1 2
19 1 2
22 1 2
23 1 2
24 1 2
51 1 2
...

A friend suggested to do it otherwise:
the table cms_allowed should contain all pages where you ARE allowed to.
Such query would indeed be much simpler 😉

Every time a page is added, you add every user to cms_allowed with the pageid of the new page

I consider this as a last option because the the table cms_allowed would contains a lot less data the way I want to do it (cms_allowed contains all pages you are NOT allowed to) with my may of working the is also less database load. Just have to find the right query... 🙂

Thanks already and Happy Newyear!

saltwater

I tought of an acceptable solution, but 2 query's are needed

First, I select the id' of the pages where you are NOT allowed to.

Second, I select all pages except for the ones that have an ID that was returned by the previous query

Acceptable? or not?

saltwater

I just turned this idea into code and it works fine...