MySQL Select values in table depending on another table

Question

I have a table 'Staff' with details of all Staff. 'StaffID' included.

Then I have a table 'StaffRole' which has 'StaffID' and 'RoleID'

I want to do something like this: Select * From Staff Where RoleID=1;

But I'm not sure if I can do this as RoleID is not in the Staff table.

Anyone know the correct syntax? Any feedback is appreciated.

possible duplicate of [How can an SQL query return data from multiple tables](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) — Maciej Cygan, Mar 16 '14 at 01:21

score 2 · Accepted Answer · answered Mar 16 '14 at 01:20

2

Try this:

SELECT * FROM staff s
WHERE EXISTS(
   SELECT 'ROLE'
   FROM staffrole r
   WHERE r.staffid = s.staffid
   AND r.roleid = 1
)

In alternative:

SELECT * FROM staff s
JOIN staffrole r
ON r.staffid = s.staffid
WHERE r.roleid = 1

answered Mar 16 '14 at 01:20

Joe Taras

score 2 · Answer 2 · answered Mar 16 '14 at 01:21

2

Use a join:

select s.*
from staffrole r
join staff s
  on s.staffid = r.staffid
where roleid = 1

An index on staffrole(roleid) should make it perform better.

answered Mar 16 '14 at 01:21

Bohemian

2 Answers2