Union Join between 3 SQL Queries

Question

Post reply

Union Join between 3 SQL Queries

michael@icsbusinessbox

SSC Rookie

Points: 29
More actions
December 11, 2013 at 8:01 am

#285930

I am looking to perform a UNION join between 3 select queries, this is what I think I need to do to get the desired result set. The SQL Query is for a report which displays emergency/next of kin contact information. I have added 'IsNextOfKin' flags to the three static tables; StaticAddressType, StaticPhoneType and StaticEmailType. For each of these tables, the desired types are 'Emergency' and 'Next Of Kin' ONLY. I cannot use 'hard coding' i.e where staticaddresstype = 'Emergency' or where staticaddresstype in ('Emergency', 'Next Of Kin') etc. I am using the flags StaticAddressType.IsNextOfKin=1, StaticEmailType.IsNextOfKin=1, StaticPhoneType.IsNextOfKin=1.
I ONLY want to return values which contain Emergency or Next Of Kin. I think i might need to make this three seperate queries and join them? a trimmed down version of my SQL query is below. I have tried to take as much of the irrelevant logic out as possible.
SELECT st.keyfield as [Title],
e.FirstName as [First Name],
e.LastName as [Last Name],
isnull(spt.PhoneType, '') as [Phone Type],
isnull(emph.Phone, '') as [Phone],
isnull([set].EmailType, '') as [Email Type],
isnull(em.Email, '') as [Email],
isnull(sat.AddressType, '') as [Address Type],
isnull(ea.Address1, '') as [Property Name],
isnull(ea.Address2, '') as [Property Number],
isnull(ea.Address3, '') as [Flat Number],
isnull(ea.Address4, '') as [Address Line 1],
isnull(ea.Address5, '') as [Address Line 2],
isnull(ea.City,'') as [City],
isnull(ea.Region, '') as [Region],
isnull(ea.Postcode,'') as [Postcode],
FROM Employee e
Left Join StaticTitle st on e.title = st.id and st.IsActive=1
left Join
(SELECT eph.EmployeeID, eph.DateFrom as DateFrom1, eph.DateTo as DateTo1, eph.Pay,
eph.EmployeePosition, eph.ForeignCurrencyCode, eph.TotalSalaryPackage,
eph.SalaryCategory, eph.Frequency, eph.FTEEquivalentAmount, eph.IsBasicSalary,
eph.IsCurrent, eph.IncludeInPayroll, eph.IsoneOff, eph.IsPegged, ej.DateFrom as DateFrom2,
ej.DateTo as DateTo2, ej.position, ej.Notes, ej.ChangeReason, ej.SalaryBand, ej.HoursPerWeek,
ej.Country, ej.PositionLocation, ej.IsCurrent as EJIsCurrent
FROMEmployeePayHistory as eph
inner Join EmployeeJob as ej on ej.Employee = eph.EmployeeID and ej.position = eph.EmployeePosition
and eph.IsActive=1 and eph.IsCurrent=1 and eph.IncludeInPayroll=1
and eph.DateFrom = ej.DateFrom) as PAP on e.EmployeeID = PAP.EmployeeID
Left Join StaticNationality sn2 on PAP.Country = sn2.IDand sn2.IsActive=1
left Join StaticRelation sr on e.NextOfKinRelation = sr.ID and sr.IsActive=1
left Join EmployeePhone emph on e.EmployeeID = emph.Employee and emph.IsActive=1
left Join EmployeeEmail em on e.EmployeeID = em.employee and em.IsActive=1
left Join EmployeeAddress ea on e.EmployeeID = ea.Employee and ea.IsActive=1
left Join StaticPhoneType spt on emph.PhoneType = spt.PhoneTypeID and spt.IsActive=1
inner Join StaticEmailType [set] on em.EmailType = [set].EmailTypeID and [set].IsActive=1
inner Join StaticAddressType sat on ea.AddressTypeID = sat.AddressTypeID and sat.IsActive=1
WHERE(e.IsActive=1)
and (sr.IsActive=1)
and (pap.EJIsCurrent=1)
and (PAP.IsCurrent=1)
and (spt.IsNextOfKin=1)or /* using OR as not ALL employees meet the three conditions */
([set].IsNextOfKin=1)or
(sat.IsNextOfKin=1)
ORDER BY e.LastName asc

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Hi and welcome to the forums!!! In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 2

I would look at this part of your where clauseand (spt.IsNextOfKin=1)or /* using OR as not ALL employees meet the three conditions */

([set].IsNextOfKin=1)or

(sat.IsNextOfKin=1)

If you want one of those three to be true and the rest of the where to be true you need to enclose these inside of parentheses

and (

(spt.IsNextOfKin=1)or /* using OR as not ALL employees meet the three conditions */

([set].IsNextOfKin=1)or

(sat.IsNextOfKin=1)

)

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Keith Tate (12/11/2013)
I would look at this part of your where clauseand (spt.IsNextOfKin=1)or /* using OR as not ALL employees meet the three conditions */ ([set].IsNextOfKin=1)or
(sat.IsNextOfKin=1)
If you want one of those three to be true and the rest of the where to be true you need to enclose these inside of parentheses
and (
(spt.IsNextOfKin=1)or /* using OR as not ALL employees meet the three conditions */
([set].IsNextOfKin=1)or
(sat.IsNextOfKin=1)
)

ORs without appropriate parentheses is one of the most common logic flaws I see in WHERE clauses at clients. They can be VERY sneaky, and difficult to "see" too!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 4

If the columns are all 1 or 0, if a + b + c > 0, one of the columns is populated.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Take care with your join types. Referencing a column from an outer-joined table will convert the join to an IJ (except the special case of checking a column for NULL).

SELECT

st.keyfield as [Title],

e.FirstName as [First Name],

e.LastName as [Last Name],

isnull(spt.PhoneType, '') as [Phone Type],

isnull(emph.Phone, '') as [Phone],

isnull([set].EmailType, '') as [Email Type],

isnull(em.Email, '') as [Email],

isnull(sat.AddressType, '') as [Address Type],

isnull(ea.Address1, '') as [Property Name],

isnull(ea.Address2, '') as [Property Number],

isnull(ea.Address3, '') as [Flat Number],

isnull(ea.Address4, '') as [Address Line 1],

isnull(ea.Address5, '') as [Address Line 2],

isnull(ea.City,'') as [City],

isnull(ea.Region, '') as [Region],

isnull(ea.Postcode,'') as [Postcode] --,

FROM Employee e

Left Join StaticTitle st on e.title = st.id and st.IsActive=1

left Join (

SELECT

eph.EmployeeID, eph.DateFrom as DateFrom1, eph.DateTo as DateTo1, eph.Pay,

eph.EmployeePosition, eph.ForeignCurrencyCode, eph.TotalSalaryPackage,

eph.SalaryCategory, eph.Frequency, eph.FTEEquivalentAmount, eph.IsBasicSalary,

eph.IsCurrent, eph.IncludeInPayroll, eph.IsoneOff, eph.IsPegged, ej.DateFrom as DateFrom2,

ej.DateTo as DateTo2, ej.position, ej.Notes, ej.ChangeReason, ej.SalaryBand, ej.HoursPerWeek,

ej.Country, ej.PositionLocation, ej.IsCurrent as EJIsCurrent

FROM EmployeePayHistory as eph

inner Join EmployeeJob as ej on ej.Employee = eph.EmployeeID and ej.position = eph.EmployeePosition

and eph.IsActive=1 and eph.IsCurrent=1 and eph.IncludeInPayroll=1

and eph.DateFrom = ej.DateFrom

) as PAP on e.EmployeeID = PAP.EmployeeID

Left Join StaticNationality sn2 on PAP.Country = sn2.ID and sn2.IsActive=1

left Join StaticRelation sr on e.NextOfKinRelation = sr.ID and sr.IsActive=1

left Join EmployeePhone emph on e.EmployeeID = emph.Employee and emph.IsActive=1

left Join EmployeeEmail em on e.EmployeeID = em.employee and em.IsActive=1

left Join EmployeeAddress ea on e.EmployeeID = ea.Employee and ea.IsActive=1

left Join StaticPhoneType spt on emph.PhoneType = spt.PhoneTypeID and spt.IsActive=1

inner Join StaticEmailType [set] on em.EmailType = [set].EmailTypeID and [set].IsActive=1

inner Join StaticAddressType sat on ea.AddressTypeID = sat.AddressTypeID and sat.IsActive=1

CROSS APPLY (

SELECT ValidRow = CASE WHEN spt.IsNextOfKin = 1 OR [set].IsNextOfKin = 1 OR sat.IsNextOfKin = 1 THEN 1 ELSE 0 END

) x

WHERE e.IsActive = 1

and sr.IsActive = 1 -- converted [sr] from outer to inner join

and pap.EJIsCurrent = 1 -- converted [pap] from outer to inner join

and PAP.IsCurrent = 1 -- converted [pap] from outer to inner join

and x.ValidRow = 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

below86 SSChampion Points: 11513 More actions · Answer 6

I recently worked on a process that had about 10 different fields I needed to check, all were had 'Y' or 'N' as the value. If any one of these were set to 'N' then that record didn't qualify. My first thought went to one big nested case statement checking each field one at a time. No one likes to deal with that and it's hard to read and to follow at times.

casewhen field1 = 'Y'

then casewhen field2 = 'Y'

thencasewhen field3 = 'Y'

So I decided to turn my thinking around:

casewhen 'N' in (field1, field2, field3, ...)

This is so simple but I had never thought about it that way. Just wanted to share, maybe just me but I get stuck thinking one way, and just stepping back for a minute to look at it differently made a huge difference to me.

For what you want and your fields being 1 or 0, I think the adding and checking for greater than zero is an easy way to do that if only one has to be 'true'.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

michael@icsbusinessbox SSC Rookie Points: 29 More actions · Answer 7

Hi, thanks for your replies.

Apologies if I wasn't being clear in my explanation.

So I need to retrieve all employees who have emergency/next of kin contact details in the system.

eg

John Smith could have emergency phone number, emergency address, next of kin email

Joan smith might only have emergency phone number

Joe Bloggs might have emergency phone number and emergency address.

Note: these employees also have work, person, other phone/email/address types, which are currently showing and do not want. This is why i have a flag on each of the static tables.

StaticAddressType.IsNextOfKin

StaticEmailType.IsNextOfKin

StaticPhoneType.IsNextOfKin

These are set to 'IsNextOfKin=1', the other types (work, personal, business, other are set to '0'

So, i want to retrieve all records where the three contact types are EMERGENCY or IS NEXT OF KIN, but cannot search for the types themselves in the where clause as this would be hard coding.

My question is, should I take the query, turn it into 3 queries, have StaticAddressType.IsNextOfKin=1 in the first query, StaticEmailType.IsNextOfKin=1 in the next query, and StaticPhoneType.IsNextOfKin=1 in the last query, and join the three queries using UNION, which i have done with no luck. See below.

(

SELECTe.EmployeeReference as [Employee Reference],

st.keyfield as [Title],

e.FirstName as [First Name],

e.LastName as [Last Name],

isnull(spt.PhoneType, '') as [Phone Type],

isnull(emph.Phone, '') as [Phone],

isnull([set].EmailType, '') as [Email Type],

isnull(sat.AddressType, '') as [Address Type],

isnull(ea.Address1, '') as [Property Name],

isnull(ea.Address2, '') as [Property Number],

FROM Employee e

Left Join StaticTitle st on e.title = st.idand st.IsActive=1

left Join

(SELECT eph.EmployeeID, eph.DateFrom as DateFrom1, eph.DateTo as DateTo1, eph.Pay,

eph.EmployeePosition, eph.ForeignCurrencyCode, eph.TotalSalaryPackage,

eph.SalaryCategory, eph.Frequency, eph.FTEEquivalentAmount, eph.IsBasicSalary,

eph.IsCurrent, eph.IncludeInPayroll, eph.IsoneOff, eph.IsPegged, ej.DateFrom as DateFrom2,

ej.DateTo as DateTo2, ej.position, ej.Notes, ej.ChangeReason, ej.SalaryBand, ej.HoursPerWeek,

ej.Country, ej.PositionLocation, ej.IsCurrent as EJIsCurrent

FROMEmployeePayHistory as eph

inner Join EmployeeJob as ej on ej.Employee = eph.EmployeeID and ej.position = eph.EmployeePosition

and eph.IsActive=1 and eph.IsCurrent=1 and eph.IncludeInPayroll=1

and eph.DateFrom = ej.DateFrom) as PAP on e.EmployeeID = PAP.EmployeeID

Left Join StaticNationality sn2 on PAP.Country = sn2.IDand sn2.IsActive=1

left Join StaticRelation sr on e.NextOfKinRelation = sr.ID and sr.IsActive=1

left Join EmployeePhone emph on e.EmployeeID = emph.Employee and emph.IsActive=1

left Join EmployeeEmail em on e.EmployeeID = em.employee and em.IsActive=1

left Join EmployeeAddress ea on e.EmployeeID = ea.Employee and ea.IsActive=1

inner Join StaticPhoneType spt on emph.PhoneType = spt.PhoneTypeID and spt.IsActive=1

inner Join StaticEmailType [set] on em.EmailType = [set].EmailTypeID and [set].IsActive=1

inner Join StaticAddressType sat on ea.AddressTypeID = sat.AddressTypeID and sat.IsActive=1

WHERE(e.IsActive=1)

and (sr.IsActive=1)

and (pap.EJIsCurrent=1)

and (PAP.IsCurrent=1)

and (spt.IsNextOfKin=1)

)

union