CODEWITHSUNDEEP
phpmysql
Alko
Alko

Reputation: 1439

php, MySql subquery from two tables

I'm trying to build a MySql query to produce results based on following:

 Table Permissions

user_id     blog_id
=========================
1           1
1           2
1           3
2           2
3           1


Table Blog

id        name
=========================
1         First Blog
2         Second Blog
3         Third Blog
4         Fourth Blog

I need to select all the records from Blog table and display it based on logged in user id like:

$user_id = $_SESSION['user_id];

Table Permissions contains access for each user allowed to view results from blog table.

So something like:

"SELECT * FROM Blog WHERE id IN()"

but I'm not sure how to access permission table to use it in IN().

So for example if user with id 1 is logged in, this user should be able to see Blogs with matching id's 1,2 and 3.

Upvotes: 1

Views: 50

Answers (2)

Yeti82
Yeti82

Reputation: 393

Try this:

SELECT * FROM Blog INNER JOIN Permissions ON Permissions.blog_id = Blog.id

This show all users.

Working code here: http://sqlfiddle.com/#!9/aa3e9/14

You can use:

SELECT * FROM Blog INNER JOIN Permissions ON Permissions.blog_id = Blog.id 
WHERE Permissions.user_id = 'user_id number'

To get a specific user.

Upvotes: 0

Shadow
Shadow

Reputation: 34244

You can use either a join, or an in() subquery.

select b.* from blogs b
inner join permissions p
on b.id=p.blog_id
where p.user_id=...

Upvotes: 1

Related Questions