Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Union Join between 3 SQL Queries Expand / Collapse
Author
Message
Posted Wednesday, December 11, 2013 8:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 5:27 AM
Points: 3, Visits: 31
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
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

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
Post #1521920
Posted Wednesday, December 11, 2013 8:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 11,970, Visits: 10,995
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1521942
Posted Wednesday, December 11, 2013 8:56 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 534, Visits: 790
I would look at this part of your where clause
and (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
Post #1521944
Posted Wednesday, December 11, 2013 9:06 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 8:43 PM
Points: 4,128, Visits: 5,836
Keith Tate (12/11/2013)
I would look at this part of your where clause
and (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 at GMail
Post #1521954
Posted Thursday, December 12, 2013 7:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:20 PM
Points: 1,146, Visits: 6,244
If the columns are all 1 or 0, if a + b + c > 0, one of the columns is populated.
Post #1522296
Posted Thursday, December 12, 2013 7:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 6,754, Visits: 12,854
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1522301
Posted Thursday, December 12, 2013 7:36 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 9:12 AM
Points: 214, Visits: 718
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.
case	when field1 = 'Y'
then case when field2 = 'Y'
then case when field3 = 'Y'

So I decided to turn my thinking around:
case	when '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'.
Post #1522314
Posted Thursday, December 12, 2013 8:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 5:27 AM
Points: 3, Visits: 31
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.





(
SELECT e.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.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
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

(
SELECT e.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.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
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 ([set].IsNextOfKin=1)
)

union

(
SELECT e.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.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
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 (sat.IsNextOfKin=1)

)
ORDER BY e.LastName asc
Post #1522346
Posted Thursday, December 12, 2013 9:13 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 9:12 AM
Points: 214, Visits: 718
What results are you getting? There is syntax errors in your posted SQL, commas before the from, I assume you removed some fields for the post.
Formatting the SQL will help us better understand what you are doing. Like this:
SELECT e.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.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
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
SELECT e.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.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
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 ([set].IsNextOfKin=1)
union
SELECT e.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.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
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 (sat.IsNextOfKin=1)
ORDER BY e.LastName asc

Without seeing your data I would think this would work, doing the union would eliminate any duplicate entries.

Test data would be needed for each table to better know, and what you are getting compared to what you are expecting.
Post #1522367
Posted Thursday, December 12, 2013 9:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 5:27 AM
Points: 3, Visits: 31
Hi, Sorry - ignore the commas. these aren't in the query. Just trimmed the select statement down to the most important fields
Post #1522370
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse