Trying To Filter Data Through Two Tables

[gummby8]

Hello I am trying to setup a form of data filtering to display certain users

 

I have 2 tables "Users" and "Assigned_rolls"

 

What I would like to do is display usernames of all users that have role #1

 

1)So the query would first get all user IDs from the assigned_roles table that have role 1

2)Then pull the names of all the returned use IDs from the users table.

3)Then display the names on a webpage.

 

I am trying to use

 

$result = mysql_query("SELECT ID FROM roles_assigned WHERE role = '1'");
$result1 = mysql_query("SELECT name FROM user WHERE ID = $result ");

 

If I change $result to a single user id that exists in sql, that users name is displayed properly. I don't know how to get the array that is created in the first query to be used as a searchable variable list in the second query so it will return any user id that had a role of 1 assigned to it in the first table.

 

Obviously I am doing something wrong here. Any help is appreciated.

Edited October 12, 2012 by gummby8

[Jessica]

Use a JOIN.

SELECT columns 
FROM table1
INNER JOIN table2
ON table1.primary_key = table2.foreign_key
WHERE table1.column = $val