Table joins - 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 2008 Forums

Transact-SQL (2008)

Table joins

Author

Topic

SQLNewbieHelp
Starting Member

3 Posts

Posted - 2014-05-16 : 13:58:18

I apologize if this is a dumb question, but I am new at this and need some help...

I have 3 tables with the following fields
EmpTable: ENAME
OccTable: ENAME, RMID
RoomTable: RMID

The EmpTable has all employees. The OccTable has only employees that are assigned a room. The RoomTable has all rooms. I am looking for all employees and all rooms. My expected results would look like:

ENAME - RMID
John Doe - RoomABC
null - RoomXYZ
Jane Doe - null

I keep getting all rooms or all employees. I can't seem to get both. Thanks in advance for your patience.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-05-16 : 14:19:41

What does your query look like? I believe you need a full join.

djj

SQLNewbieHelp
Starting Member

3 Posts

Posted - 2014-05-16 : 14:29:07

I've tried:
SELECT EmpTable.ENAME, RoomTable.RMID
FROM EmpTable full OUTER JOIN
OccTable ON EmpTable.ENAME = OccTable.ENAME FULL OUTER JOIN
RoomTable ON OccTable.RMID = RoomTable.RMID

I've also tried right outer joins.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-16 : 14:38:44

Here is one way

DECLARE @Emp TABLE (ENAME VARCHAR(10))
DECLARE @Occ TABLE (ENAME VARCHAR(10), RMID VARCHAR(10))
DECLARE @Room TABLE (RMID VARCHAR(10))

INSERT @Emp VALUES
('John'), ('Jane')

INSERT @Occ VALUES
('John', 'Room1')

INSERT @Room VALUES
('Room1'), ('Room2')


SELECT 
	Emp.ENAME
	,Room.RMID
FROM 
	@Emp AS Emp
LEFT OUTER JOIN
	@Occ AS Occ
	ON Emp.ENAME = Occ.ENAME
FULL OUTER JOIN
	@Room AS Room
	ON Occ.RMID = Room.RMID

SQLNewbieHelp
Starting Member

3 Posts

Posted - 2014-05-16 : 15:30:07

I tried that too. It's giving me the rooms with the assigned employees, but not the employees that do not have an assigned room. In my example, it's not giving me the Jane Doe - null.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources