Select question. - 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 2000 Forums

Transact-SQL (2000)

Select question.

Author

Topic

poutine
Starting Member

1 Post

Posted - 2006-11-25 : 20:33:18

So I got those 3 tables, let's say EMP, DEPT and EMP_DEPT

The PK of EMP is empno, the PK of dept is deptno,
and the PK of EMP_DEPT = empno + deptno (FKs)

So I want to select everything in EMP + the deptno that is in EMP_DEPT.

Doing this: select * from EMP, EMP_DEPT where EMP.EMPNO=EMP_DEPT.EMPNO

would return every EMP that have a DEPTNO attached to them, but what I want is every EMP, and their deptnos if they have one or more than one.

Example:

empno     deptno
bob         10
bob         20
bob         30
john
jack        30
jack        20
josh

How could I do that?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-25 : 21:09:51

You need to use an outer join, something like this

select * 
from EMP
left outer join EMP_DEPT on EMP.EMPNO=EMP_DEPT.EMPNO

Here are some places where you can learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-26 : 04:29:50

SELECT e.*, d.*
FROM Emp e
LEFT JOIN emp_dept ed on ed.empno = e.empno
left join dept d on d.deptno = ed.deptno

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources