Select using UNION returing duplicates

Question

This view is working well. Its used in a report building wizard talking to a SQLServer 2008 R2

CREATE view vwQry_LicencePayments 
as

select
P.*,
LT.LicenceTypeDesc, FT.FeeTypeDesc, FT.ThroughputText, FT.Prorata as IsProrata, PT.PaymentStatusDesc, PT.IsPaid,
E.EntityType as HolderEntityType, E.EntityTypeDesc as HolderEntityTypeDesc, E.PersonTitle, E.PersonFirstname, E.PersonSurname, 
E.OrganisationName, E.FullName as HolderFullName, 
E.MailAddress as HolderMailAddress, E.Suburb as HolderSuburb, E.State as HolderState, E.Postcode as HolderPostcode, 
E.Mobile as HolderMobile, E.Email, E.PFN as HolderPFN
from
rptPayment P
inner join vwQry_xhsEntity_Base E on P.HolderHistoryID = E.HistoryID
inner join LicenceType LT on P.LicenceTypeID=LT.LicenceTypeID
inner join LicenceFeeType FT on P.FeeTypeID=FT.FeeTypeID
inner join PaymentStatusType PT on P.PaymentStatusID = PT.PaymentStatusID 

union ALL

select
P.*,
LT.LicenceTypeDesc, FT.FeeTypeDesc, FT.ThroughputText, FT.Prorata as IsProrata, PT.PaymentStatusDesc, PT.IsPaid,
E.EntityType as HolderEntityType, E.EntityTypeDesc as HolderEntityTypeDesc, E.PersonTitle, E.PersonFirstname, E.PersonSurname, 
E.OrganisationName, E.FullName as HolderFullName, 
E.MailAddress as HolderMailAddress, E.Suburb as HolderSuburb, E.State as HolderState, E.Postcode as HolderPostcode, 
E.Mobile as HolderMobile, E.Email, E.PFN as HolderPFN
from 
(select * from rptPayment where HolderHistoryID is null) P
inner join vwQry_Entity E on P.HolderID = E.EntityID
inner join LicenceType LT on P.LicenceTypeID=LT.LicenceTypeID
inner join LicenceFeeType FT on P.FeeTypeID=FT.FeeTypeID
inner join PaymentStatusType PT on P.PaymentStatusID = PT.PaymentStatusID

A requirement to include a payment Date (LP.dateTimeStamp) came up. So adding the extra field (LP.dateTimeStamp) and the two joins (licenceFeePayment tble and licencePayment tble ) necessarily has caused duplicates in the result set. I assume becasue for example if a licence has been paid for in three separate payments it will have three records in the licencefeePayment table. They change I made is:

CREATE view vwQry_LicencePayments 
as

select
P.*,
LT.LicenceTypeDesc, FT.FeeTypeDesc, FT.ThroughputText, FT.Prorata as IsProrata, PT.PaymentStatusDesc, PT.IsPaid,
E.EntityType as HolderEntityType, E.EntityTypeDesc as HolderEntityTypeDesc, E.PersonTitle, E.PersonFirstname, E.PersonSurname, 
E.OrganisationName, E.FullName as HolderFullName, 
E.MailAddress as HolderMailAddress, E.Suburb as HolderSuburb, E.State as HolderState, E.Postcode as HolderPostcode, 
E.Mobile as HolderMobile, E.Email, E.PFN as HolderPFN, LP.DatetimeStamp AS  DatetimeStamp
from
rptPayment P
inner join vwQry_xhsEntity_Base E on P.HolderHistoryID = E.HistoryID
inner join LicenceType LT on P.LicenceTypeID=LT.LicenceTypeID
inner join LicenceFeeType FT on P.FeeTypeID=FT.FeeTypeID
inner join PaymentStatusType PT on P.PaymentStatusID = PT.PaymentStatusID 
       inner join licenceFeePayment LFP on P.licenceID = LFP.licenceID
       inner join licencePayment LP on LFP.paymentID = LP.paymentID

union ALL

select
P.*,
LT.LicenceTypeDesc, FT.FeeTypeDesc, FT.ThroughputText, FT.Prorata as IsProrata, PT.PaymentStatusDesc, PT.IsPaid,
E.EntityType as HolderEntityType, E.EntityTypeDesc as HolderEntityTypeDesc, E.PersonTitle, E.PersonFirstname, E.PersonSurname, 
E.OrganisationName, E.FullName as HolderFullName, 
E.MailAddress as HolderMailAddress, E.Suburb as HolderSuburb, E.State as HolderState, E.Postcode as HolderPostcode, 
E.Mobile as HolderMobile, E.Email, E.PFN as HolderPFN, LP.DatetimeStamp AS  DatetimeStamp
from 
(select * from rptPayment where HolderHistoryID is null) P
inner join vwQry_Entity E on P.HolderID = E.EntityID
inner join LicenceType LT on P.LicenceTypeID=LT.LicenceTypeID
inner join LicenceFeeType FT on P.FeeTypeID=FT.FeeTypeID
inner join PaymentStatusType PT on P.PaymentStatusID = PT.PaymentStatusID
       inner join licenceFeePayment LFP on P.licenceID = LFP.licenceID
       inner join licencePayment LP on LFP.paymentID = LP.paymentID

Help would be very much appreciated. How can I join these two tables and avoid the duplicates?

As @registered user has pointed out, although I have done so, its not as simple as removing ALL from my UNION.

@registered user thank you for your helpful reply. I have made a change to my query based on your help, now have another little issue. I have added a field called 'PaymentTypeDesc' which is from the 'paymentType' table. As this table joins via the 'LicencePayment' I had to place it in your sub query. I am still getting some duplicates because I now believe, not only can multiple payments come in for a licence, they can come from multiple different sources EG EFTPOS, CASH etc....

How can I cater for not only multiple payment transaction but also multiple payment types? Here is my altered working query after using your help:

CREATE view vwQry_LicencePaymentsNew 
as

select
P.*,
LT.LicenceTypeDesc, FT.FeeTypeDesc, FT.ThroughputText, FT.Prorata as IsProrata, PT.PaymentStatusDesc, PT.IsPaid,
E.EntityType as HolderEntityType, E.EntityTypeDesc as HolderEntityTypeDesc, E.PersonTitle, E.PersonFirstname, E.PersonSurname, 
E.OrganisationName, E.FullName as HolderFullName, 
E.MailAddress as HolderMailAddress, E.Suburb as HolderSuburb, E.State as HolderState, E.Postcode as HolderPostcode, 
E.Mobile as HolderMobile, E.Email, E.PFN as HolderPFN
    , lic.VehicleRegNo as VehicleRegNo 
    , payment.PaymentTypeDesc as PaymentTypeDesc
    , S.StatusTypeDesc as StatusTypeDesc
    , Payment.LastPaymentDate as DatetimeStamp
    --  , Payment.FirstPaymentDate 
from
rptPayment P
inner join vwQry_xhsEntity_Base E on P.HolderHistoryID = E.HistoryID
inner join LicenceType LT on P.LicenceTypeID=LT.LicenceTypeID
inner join LicenceFeeType FT on P.FeeTypeID=FT.FeeTypeID
inner join PaymentStatusType PT on P.PaymentStatusID = PT.PaymentStatusID 
inner join licence Lic on P.licenceID = lic.licenceID
inner join vwLicCurrentStatus S on P.LicenceID=S.LicenceID
inner join 
    (SELECT LFP.licenceID
        , FirstPaymentDate = MIN(LP.DatetimeStamp)
        , LastPaymentDate = MAX(LP.DatetimeStamp)
        , PType.PaymentTypeDesc
    FROM licenceFeePayment LFP 
    INNER JOIN licencePayment LP 
        on LFP.paymentID = LP.paymentID
    INNER JOIN paymentType PType 
        on LP.paymentTypeID = PType.paymentTypeID
    GROUP BY lfp.licenceID, PType.PaymentTypeDesc) Payment
    ON P.licenceID = Payment.licenceID

union

select
P.*,
LT.LicenceTypeDesc, FT.FeeTypeDesc, FT.ThroughputText, FT.Prorata as IsProrata, PT.PaymentStatusDesc, PT.IsPaid,
E.EntityType as HolderEntityType, E.EntityTypeDesc as HolderEntityTypeDesc, E.PersonTitle, E.PersonFirstname, E.PersonSurname, 
E.OrganisationName, E.FullName as HolderFullName, 
E.MailAddress as HolderMailAddress, E.Suburb as HolderSuburb, E.State as HolderState, E.Postcode as HolderPostcode, 
E.Mobile as HolderMobile, E.Email, E.PFN as HolderPFN
    , lic.VehicleRegNo as VehicleRegNo 
    , payment.PaymentTypeDesc as PaymentTypeDesc
    , S.StatusTypeDesc as StatusTypeDesc
    , Payment.LastPaymentDate as DatetimeStamp
    --  , Payment.FirstPaymentDate 

from rptPayment P
inner join vwQry_xhsEntity_Base E on P.HolderHistoryID = E.HistoryID
inner join LicenceType LT on P.LicenceTypeID=LT.LicenceTypeID
inner join LicenceFeeType FT on P.FeeTypeID=FT.FeeTypeID
inner join PaymentStatusType PT on P.PaymentStatusID = PT.PaymentStatusID 
inner join licence Lic on P.licenceID = lic.licenceID
inner join vwLicCurrentStatus S on P.LicenceID=S.LicenceID
inner join 
    (SELECT LFP.licenceID
        , FirstPaymentDate = MIN(LP.DatetimeStamp)
        , LastPaymentDate = MAX(LP.DatetimeStamp)
        , PType.PaymentTypeDesc
    FROM licenceFeePayment LFP 
    INNER JOIN licencePayment LP 
        on LFP.paymentID = LP.paymentID
    INNER JOIN paymentType PType 
        on LP.paymentTypeID = PType.paymentTypeID

    GROUP BY lfp.licenceID, PType.PaymentTypeDesc) Payment
    ON P.licenceID = Payment.licenceID

@Ken thank you for your time. I have added to my post which still has a problem I would liek help with. As Registered User pointed out its not as simple as removing ALL — Mat41, May 27 '13 at 04:20
You need to provide more info then (like some sample data and the output you want from that data). Without it, we can only guess what the problem might be, and that's not how StackOverflow works. — Ken White, May 27 '13 at 04:31
I do know which fields/joins was giving me the unwanted records and I thought I explained it clearly therefore no need for the data. however I understand your comment and are aware the output would have also been useful, I will include this information next time. Thanks — Mat41, May 27 '13 at 22:26

Registered User · Accepted Answer · 2013-05-27T21:14:01.783

The fundamental business problem you need to solve first is determine how you want to present the data to end users. In the case of the LastPaymentDate, you basically decided the last DatetimeStamp is the winning record. This record was picked by using the simplest method: the maximum value of the column in a subquery. If you have multiple payment type descriptions, sources of payments, or some other payment method attribute, then you either need to pick one winner or aggregate the values together. This leaves you with several options:

If you only want attributes of the last payment, then you could use the ROW_NUMBER() aggregate window function and then restrict your join to the data to the last row. This can be expensive for really large data sets, but you could assign the values either in the transactional system itself or on a partial set of data in a general ETL process. If the data set is relatively small then the cost may be trivial.
If there is a reliable last modified date in each table, you could run a subquery to return the maximum last modified date and filter to the row that meets this condition. This approach can also perform poorly and may also not work well if multiple rows have identical last modified dates, then you may still return multiple rows.
You could concatenate together all the selected payment methods, sources, or other attributes into one field. This generally is a poor solution since users may need the values separated out, but in some cases this is exactly the right solution. You need to work the business owners to determine if this is the right solution or not.
You could speak to the business owners who requested this data and explain how the data works and the possible permutations of how you could provide them with the data. Some users may want the last payment attributes, some may want concatenated data, some may want the whole history which means you may need to provide a separate non-aggregated payment details report. I would recommend reviewing this with stakeholders before making assumptions, coding your solution, and finding out you need to recode the whole solution since it doesn't meet the needs of the business.

ORIGINAL RESPONSE BELOW

If the LicensePayment table includes multiple distinct values, then changing UNION ALL to UNION will not be sufficient. You will need to decide whether or not this table needs to get the first payment date, last payment date, or both in separate fields. You can do this by: (1) converting the join to the LicensePayment table to a subquery, or (2) aggregating the LicensePayment data separately and joining to the aggregated table. Here's a sample query using the first methodology:

CREATE view vwQry_LicencePayments 
as

select
P.*,
LT.LicenceTypeDesc, FT.FeeTypeDesc, FT.ThroughputText, FT.Prorata as IsProrata, PT.PaymentStatusDesc, PT.IsPaid,
E.EntityType as HolderEntityType, E.EntityTypeDesc as HolderEntityTypeDesc, E.PersonTitle, E.PersonFirstname, E.PersonSurname, 
E.OrganisationName, E.FullName as HolderFullName, 
E.MailAddress as HolderMailAddress, E.Suburb as HolderSuburb, E.State as HolderState, E.Postcode as HolderPostcode, 
E.Mobile as HolderMobile, E.Email, E.PFN as HolderPFN
    , Payment.FirstPaymentDate
    , Payment.LastPaymentDate
from
rptPayment P
inner join vwQry_xhsEntity_Base E on P.HolderHistoryID = E.HistoryID
inner join LicenceType LT on P.LicenceTypeID=LT.LicenceTypeID
inner join LicenceFeeType FT on P.FeeTypeID=FT.FeeTypeID
inner join PaymentStatusType PT on P.PaymentStatusID = PT.PaymentStatusID 
inner join 
    (SELECT LFP.LicenseID
        , FirstPaymentDate = MIN(LP.DatetimeStamp)
        , LastPaymentDate = MAX(LP.DatetimeStamp)
    FROM licenceFeePayment LFP 
    INNER JOIN licencePayment LP 
        on LFP.paymentID = LP.paymentID
    GROUP BY lfp.LicenseID) Payment
    ON P.licenceID = Payment.licenceID
union ALL
select
P.*,
LT.LicenceTypeDesc, FT.FeeTypeDesc, FT.ThroughputText, FT.Prorata as IsProrata, PT.PaymentStatusDesc, PT.IsPaid,
E.EntityType as HolderEntityType, E.EntityTypeDesc as HolderEntityTypeDesc, E.PersonTitle, E.PersonFirstname, E.PersonSurname, 
E.OrganisationName, E.FullName as HolderFullName, 
E.MailAddress as HolderMailAddress, E.Suburb as HolderSuburb, E.State as HolderState, E.Postcode as HolderPostcode, 
E.Mobile as HolderMobile, E.Email, E.PFN as HolderPFN
    , Payment.FirstPaymentDate
    , Payment.LastPaymentDate
from rptPayment P
inner join vwQry_Entity E on P.HolderID = E.EntityID
inner join LicenceType LT on P.LicenceTypeID=LT.LicenceTypeID
inner join LicenceFeeType FT on P.FeeTypeID=FT.FeeTypeID
inner join PaymentStatusType PT on P.PaymentStatusID = PT.PaymentStatusID
inner join 
    (SELECT lfp.LicenseID
        , FirstPaymentDate = MIN(LP.DatetimeStamp)
        , LastPaymentDate = MAX(LP.DatetimeStamp)
    FROM licenceFeePayment LFP 
    INNER JOIN licencePayment LP 
        on LFP.paymentID = LP.paymentID
    GROUP BY lfp.LicenseID) Payment
    ON P.licenceID = Payment.licenceID
WHERE p.HolderHistoryID is null;

score 0 · Answer 2 · edited May 23 '17 at 11:48

0

As Ken points out, you have to use UNION, not UNION ALL.

See also What is the difference between UNION and UNION ALL?

edited May 23 '17 at 11:48

Community

1
1

answered May 27 '13 at 01:29

chue x

18,573
7
56
70

This won't help if the LP.DatetimeStamp is different between payments -- which it likely is if the field represents the different payment dates. – Registered User May 27 '13 at 01:35

Select using UNION returing duplicates

2 Answers2