Retrieving aggregated results from two queries

Question

I am trying to figure out best way to compare results from 2 queries and display the difference.

• Table 1 = User Table
• Table 2 = Page Table
• Table 3 = Assigned table

Example: There are users 1 and users 2 in the user table. User 1 has been assigned to 10 pages, user 2 has been assigned to only 1 page.

This works fine for finding which pages they have assigned, which is only 1 page for this example.

SELECT * FROM assigned_table WHERE user= 2

But I can not figure out how to get results of all the other pages it doesn't have access to.

This does not work because there is user 1 that has access to all 10, so it gets the results of all other users except user2

SELECT * FROM assigned_table WHERE user != 2

So basically I need it to say what pages does user2 have access to, and then which pages does it not have access to and display both results separately

Any assistance would be appreciated.

Sorry if similar topic was posted elsewhere, was unable to find what I was looking for.

Answer 1

You should use a join for this. Here's the documentation on this https://dev.mysql.com/doc/refman/5.0/en/join.html.

Something like

select pt.pagename from `Assigned table` as at
join `Page Table` as pt
on at.pageid = pt.id
where at.user = 2

This would give you a listing of all page titles (pagename) user 2 has assigned to him/her. Your column and table names will need to be updated.

Pull all papers not assigned to specific user.

select pagename 
from `Page Table` 
where id not in (select pageid from `Assigned table` where at.user = 2)