Query Multiple Tables II

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ripcurlksm
Forum Commoner
Posts: 34
Joined: Sun Aug 08, 2004 9:17 pm

Query Multiple Tables II

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Re: Query Multiple Tables II

Post 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?
ripcurlksm
Forum Commoner
Posts: 34
Joined: Sun Aug 08, 2004 9:17 pm

Post by ripcurlksm »

Wrong forum, whoops sorry. Thanks for moving it. :)

I use the username:
$_SESSION['valid_user'] = $username;
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
Post Reply