Simple MySQL Two Table Query

Question

I have two tables

1st table :-

id name dept
1  John dept1
2  Mary dept2
3  Dave dept3
4  John dept4
5  John dept5


2nd table :-

id submitter dept
1  Rupert    dept3
2  Joe       dept1
3  Lisa      dept2
4  Louise    dept4
5  Tom       dept5

what i would like is a query to allow people in the name column in the first table to only show records based on their matching departments eg John in table one will return the 3 records in table 2 (id 2,4 and 5)

So far i have tried SELECT * FROM table1, table2 WHERE table1.dept = table2.dept AND table1.name='John'

This is where im stuck - so far i have tried SELECT * FROM table1, table2 WHERE table1.dept = table2.dept AND table1.name='John' — Retroisbest, Nov 17 '17 at 12:03
You have many unclosed questions (IMHO) with possible solutions; they should be marked as solved in order to properly close those questions, including this one. — Funk Forty Niner, Nov 17 '17 at 12:09

Sorix · Accepted Answer · 2017-11-17T12:11:36.313

1

If I correctly understand your problem, you need to make a join between the two tables using the field dept and filter your results by the name of the requester, in the first table.

SELECT t2.submitter, t2.dept
FROM table1 t1
LEFT JOIN table2 t2 ON t1.dept = t2.dept
WHERE t1.name = :person_name

Documentation and examples (you can also look at the left menu at Inner, right, full and self join's).

edited Nov 17 '17 at 12:11

answered Nov 17 '17 at 12:07

Sorix

850
5
18

Simple MySQL Two Table Query

1 Answers1