PebHmong Discussion Forum
Hobbies & Interests => Computer & Technology => Topic started by: npis on March 27, 2012, 08:01:49 PM
-
I have two tables employee and employee_addre ss
employee have name, status, id
john, active, 001
tom, active, 002
josh, inactive, 003
tammy, activce, 004
debbie, active, 005
sammy, active, 006
employee_addre ss have id, address, city, state
002, 123 johnson st, st. paul, mn
005, 456 fifth st, minnneapolis, mn
I want to list all active employees and if exist their address. Output shoud be
john
tom, 123 johnson st, st. paul, mn
tammie
debbie, 456 fifth st, minnneapolis, mn
sammy
I'm doing it with the inner join but it's not giving me the result i want.
-
I have two tables employee and employee_addre ss
employee have name, status, id
john, active, 001
tom, active, 002
josh, inactive, 003
tammy, activce, 004
debbie, active, 005
sammy, active, 006
employee_addre ss have id, address, city, state
002, 123 johnson st, st. paul, mn
005, 456 fifth st, minnneapolis, mn
I want to list all active employees and if exist their address. Output shoud be
john
tom, 123 johnson st, st. paul, mn
tammie
debbie, 456 fifth st, minnneapolis, mn
sammy
I'm doing it with the inner join but it's not giving me the result i want.
What is your sql statement?
-
select employee.name,
employee_addre ss.address,
employee_addre ss.city,
employee_addre ss.state,
employee_addre ss.zip
From employee INNER JOIN employee_addre ss ON employee.id = employee_addre ss.id
where employee.statu s = 'active';
This only gives me the employee whose active and have an address. I want all active employees and address if exist. If not exist just leave address related field blank.
-
OTB,
Thanks for the reply. I found it. Instead of INNER JOIN, I had to use LEFT OUTER JOIN.
-
use "left outer join". this would give you your set of data and the other.