Query Multiple Tables II
Posted: Mon Jan 01, 2007 6:08 pm
I have three tables set up: user, reports & permissions. I need some help in executing a SQL query and I am having a tough time getting the right results.
Summary:
I have a login setup so that a user can login and view reports that they have permission to see. I need the SQL statement to get the user's id, then select all of the rows in the "permissions" table where there id is present AND get the "report id" associated inside that table, THEN select all of the rows in the "report" table that have the "report id" from the previous query.
Here is my database schema:
Here is my incomplete SQL query. I still need to reference the permissions table and I am really lost on this one.
SELECT reports.* FROM user, report WHERE user.id = reports.id;
Can anyone suggest a correct query?
Summary:
I have a login setup so that a user can login and view reports that they have permission to see. I need the SQL statement to get the user's id, then select all of the rows in the "permissions" table where there id is present AND get the "report id" associated inside that table, THEN select all of the rows in the "report" table that have the "report id" from the previous query.
Here is my database schema:
Code: Select all
user
==================
id | username
==================
1 | bob
2 | john
reports
===============================
id | title | company
===============================
1 | Green | Crayola
2 | Red | Bic
3 | Blue | Papermate
permissions
===========================
user_id | report_id
===========================
1 | 1
1 | 2
2 | 1
2 | 3
**So Bob would get "Green" and "Red" and John would get "Red" and "Blue"**Here is my incomplete SQL query. I still need to reference the permissions table and I am really lost on this one.
SELECT reports.* FROM user, report WHERE user.id = reports.id;
Can anyone suggest a correct query?