Union Query - 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)

Union Query

Author

Topic

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-06-02 : 14:46:26

I have the following 3 tables:

declare @Employee table (
ID int,
Name varchar(20)
)

insert into @Employee (
ID,
Name
)
values
(1, 'Bob'),
(2, 'Doug'),
(3, 'Bill')

declare @AbsenceType table (
ID int,
Descrip varchar(50)
)

insert into @AbsenceType (
ID,
Descrip
)
values
(1, 'Sick'),
(2, 'Flex'),
(3, 'Vacation')

declare @AbsenceHistory table (
ID int,
EmployeeId int,
AbsLength float,
AbsType int
)

insert into @AbsenceHistory (
ID,
EmployeeId,
AbsLength,
AbsType
)
values
(1, 1, 1, 1),
(2, 1, 1, 2),
(3, 2, 1, 1),
(4, 2, 4, 3),
(5, 3, 1, 2),
(6, 3, 5, 3)

Using this select statement I am getting the Name, Dscription, and Length of an employees absence history:

SELECT
Name, AT.Descrip, AH.AbsLength
FROM
@Employee E, @AbsenceHistory AH, @AbsenceType AT
WHERE
E.ID IN (1,2) AND
AH.AbsType IN (1,2,3) AND
AH.EmployeeID = E.ID AND
AH.AbsType = AT.ID
ORDER By Name

I want to have the query return a 0 for the absence types that the employee doesn't have.

So with the above query I want it to return:

Name Description AbsLength
Bob Sick 1
Bob Flex 1
Bob Vacation 0
Doug Sick 1
Doug Vacation 4
Doug Flex 0

I am trying to use a Union, but am not getting the results I need.

Can someone please help me out?

Thanks.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-02 : 17:03:33

SELECT
Name, AT.Descrip, coalesce(AH.AbsLength,0)
FROM
@Employee E
cross join @AbsenceType AT
left join @AbsenceHistory AH
on AH.EmployeeID = E.ID AND
and AH.AbsType = AT.ID
WHERE
E.ID IN (1,2) AND
AT.ID IN (1,2,3) -- is this needed?
ORDER By Name

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-06-03 : 15:07:51

Awesome!!

Thanks so much Nigel, much appreciated!!

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-06-03 : 15:38:55

I missed something, in my real query there is a data column in the AbsenceHistory column that is used by the user to filter by date.

So the query is more like this:

SELECT
Name, AT.Descrip, coalesce(AH.AbsLength,0)
FROM
@Employee E
cross join @AbsenceType AT
left join @AbsenceHistory AH
on AH.EmployeeID = E.ID AND AH.AbsType = AT.ID
WHERE
E.ID IN (1,2)AND
AT.ID IN (1,2,3) AND
AND AH.StartDate >= '01-01-2000' AND AH.EndDate <= '01-01-2012'
ORDER By Name

How can that be included and still show the same results?

Thanks.

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-06-22 : 13:04:27

Anyone?

I need to add in the date filter, but the query needs to return the data the same way it does with nigelrivetts solution:

SELECT
Name, AT.Descrip, coalesce(AH.AbsLength,0)
FROM
@Employee E
cross join @AbsenceType AT
left join @AbsenceHistory AH
on AH.EmployeeID = E.ID AND
and AH.AbsType = AT.ID
WHERE
E.ID IN (1,2) AND
AT.ID IN (1,2,3)
ORDER By Name

Need to filter by date like this:

SELECT
Name, AT.Descrip, coalesce(AH.AbsLength,0)
FROM
@Employee E
cross join @AbsenceType AT
left join @AbsenceHistory AH
on AH.EmployeeID = E.ID AND AH.AbsType = AT.ID
WHERE
E.ID IN (1,2)AND
AT.ID IN (1,2,3) AND
AND AH.StartDate >= '01-01-2000' AND AH.EndDate <= '01-01-2012' ORDER By Name

However, this filter removes the rows where the AbsLength would be 0.

Thanks!!

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-06-24 : 12:33:27

move your date condition into left join clause.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources