Page 1 of 1

Query Multiple Tables II

Posted: Mon Jan 01, 2007 6:08 pm
by ripcurlksm
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:

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?

Posted: Mon Jan 01, 2007 6:12 pm
by feyd
Why was this in PHP - Code?

Moved to Databases.
[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:1. Select the correct board for your query. Take some time to read the guidelines in the sticky topic.

Re: Query Multiple Tables II

Posted: Mon Jan 01, 2007 6:13 pm
by volka
ripcurlksm wrote: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
Do you start some kind of session when a user logs in? Probably. What session data do you store? The username or the userid? Or maybe both?

Posted: Mon Jan 01, 2007 6:16 pm
by ripcurlksm
Wrong forum, whoops sorry. Thanks for moving it. :)

I use the username:
$_SESSION['valid_user'] = $username;

Posted: Mon Jan 01, 2007 10:33 pm
by volka
Would be easier if you stored the userid (too), $_SESSION['userid'].
You do not need the table `user` in your query then
ripcurlksm wrote: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;
only select those records having permissions.userid=$_SESSION[userid] and matching reports ids.