[SOLVED] Select query / loop problem

[realjumper]

Hi,

 

My users are organised in a table named 'users'. Each user is a member of a department, but some users are members of more than one department, so I have another table named 'user_department'. I have another table named 'submissions' int which users submissions are stored. Right now I am developing an archive of submissions. Generally speaking a simple query will do the job like this:

 

$query = "SELECT * FROM submissions WHERE status != 'Pending' AND sid = '$username' ORDER BY id desc LIMIT $from, $max_results";
$result = mysql_query($query);

 

The problem comes when I try to select only the submissions lodged by the users department. The point being that the department head can view all submissions lodged by his department. If the department head is only head of a single department, this would work:

 

$query = "SELECT * FROM submissions WHERE status != 'Pending' AND department = '$users_department' ORDER BY id desc LIMIT $from, $max_results";
$result = mysql_query($query);

 

But if the department head is head of more than one department, I start to get lost! I can loop through the 'user_department' table to find out which departments the user is the head of like this:

 

$result2 = mysql_query("SELECT * FROM user_department WHERE sid='$username'") 
or die(mysql_error()); 

while($row2 = mysql_fetch_array( $result2 )) {

echo $row99['department'], ;

}

 

So lets say the above loop returns 'History, Science'.

 

How can I now create a query that would look like this....in laymans terms?

 

$query = "SELECT * FROM submissions WHERE status != 'Pending' AND department = 'History' AND department = 'Science' ORDER BY id desc LIMIT $from, $max_results";
$result = mysql_query($query);

 

It would seem that I have to have the results of the loop in the query somehow, but I can't seem to get a start on how to do this.

 

Any help would be great. Thanks

[realjumper]

Never mind, I think I've got it. The answer is in the query which I need to make an inner join of the two tables....and then loop through the results