SQL select based on 2 tables 1

[RosieGp]

Hi,
I have another issue today...
In my DB I have 2 tables. Employee_Info, Employee_Address.
I'm trying to retrieve information from the Employee_Info based on a condition in Employee_Address.
Employee_Info
ID Name EyeColor Job_Status
1 Joe black Y
2 Dan brown N
3 Sam brown Y

Employee_Address
ID Name Address
1 Joe 102 W.Island Road
2 Dan 345 N.Way Land
3 Sam 8790 Yvonne Road

Now my query has to pick ID, NAme and Address from Employee_Address for only those employees who have Job_Status = 'Y' in the Employee_Info table

so something like:

select ID, Name, Address
from Employee_Address
(where Job_Status = 'Y'
IN Employee_Info)

How do I make it happen...
any help is appreciated...

 

[markros]

Select ID, Name, Address 
from Employee_Address EA
where exists (select 1
from Employee_Info EI
where EI.Job_Status = 'Y'
and EI.ID = EA.ID) -- assuming these two tables match on ID column

PluralSight Learning Library

 

[RosieGp]

thanks markos