joining? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

joining?

Author

Topic

sqlclarify
Yak Posting Veteran

56 Posts

Posted - 2008-10-05 : 21:40:37

Ok, I am confusing myself.

I want to get all the employees which placed orders.

I have select empname
from employee, orders
where employees.empkey = orders.empkey

I also have select cityname
from city

when I combine these two

I have select empname ,citiname
from employee, orders
where employees.empkey = orders.empkey
and employees.citikey = citi.empkey

What I don't understand is now won't I get less employees, that is now the meaning of my employees column is going to change, instead of being employees who placed orders its going to be employees who placed orders IN those cities.

I guess the process is starting to confuse me?? How would I combine the two name and citiname without changing the meaning

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 00:11:28

it should be

select empname ,citiname
from employee, orders,city
where employees.empkey = orders.empkey
and employees.citikey = city.empkey

you wont get less employees unless you've employee records that are not attached to city. it just returns the related city info also along with employee details. just in case you dont have city info for some employee you can use below

select empname ,coalesce(citiname,'No City') AS city
from employee e
join orders o
on o.empkey = e.empkey
left join city c
on  c.empkey=e.citikey

this will return employee details evn if they're not associated to city. city value will be NULL in those cases which we can convert to default value using coalesce.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources