Two tables, distinct license number to return

Question

Have two tables which display owner information for their registered dogs; one for 2018 and another for 2019. Each table has the same columns, names, etc.

I need to only list the one full record by the license number. For example, the dog may not have been registered in 2019, but was registered in 2018. So I need to list the 2018 record if it's not contained in the 2019 table. If there are two records (one for 2018 and another for 2019), I only want to the 2019 record listed.

Union doesn't work namely because the "RegNumber" or dog tag numbers changes from year to year; not to mention possible address changes, etc. I've tried a combination of union, intersect and except and nothing seems to work properly. Any ideas?

SELECT [License Number] AS [LicenseNumber], [Last Name] AS [LastName], [First Name] AS [Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], [Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name] AS [DogName], [Dog Breed] AS [Breed], [Purchased] AS [Year], [Sex], [Altered], [Color], [Vet] AS [Veterinarian], [RabieTag] AS [RabiesTagNumber], [RabiesTag_Exp] AS [RabiesTagExpiration], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]
FROM [dbo].[Dog_Info_2019]
WHERE Flag is not null

UNION

SELECT [License Number] AS [LicenseNumber], [Last Name] AS [LastName], [First Name] AS [Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], [Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name] AS [DogName], [Dog Breed] AS [Breed], [Purchased] AS [Year], [Sex], [Altered], [Color], [Vet] AS [Veterinarian], [RabieTag] AS [RabiesTagNumber], [RabiesTag_Exp] AS [RabiesTagExpiration], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]
FROM [dbo].[Dog_Info_2018] 
WHERE Flag is not null

ORDER BY [LicenseNumber]

When I execute the above query, both 2018 and 2019 records are shown for a total of 22569 records; 11176 rows are in the 2018 table and 11176 rows are in the 2019 table.

I've even tried NOT IN, but this only lists the records which aren't in one table or the other.

Thanks..

Sample data and desired results would really help. What do *you* mean by "full record", for instance? — Gordon Linoff, Jan 07 '20 at 19:59
Do you have separate tables for each year?!? Have one table instead, for all years. — jarlh, Jan 07 '20 at 20:00
You've stated the `Reg Number` and `Address` may change between years, what is the minimum set of matching columns that identifies a unique dog? Is `License Number` unique to the dog? — Derrick Moeller, Jan 07 '20 at 20:09
Sample data example (trimmed): LicenseYear LicenseNumber LastName Firstname StreetName Address2 City State Zip Phone Email RegNumber DogName Breed Year Sex Altered Color Veterinarian RabiesTagNumber RabiesTagExpiration OwnerID TagNumber 2018 1000 Smith Susan 111 Main Street NULL Somewhere TN 11231 1234567890 02138 Maggie Cock A Poo 2019 1015 Miller Rita/Donald 222 Water Street NULL Someplace TN 11283 1234567890 19-09836 Ziva Boxer Mix 2018 1015 Miller Rita/Donald 222 Water Street NULL Someplace TN 11283 1234567890 09731 Ziva Boxer Mix — user1424532, Jan 07 '20 at 20:25
Full Record means all the owner and dog information; the full select statement. Yes, each year has dog information for that year and only that year. Reg Number is the dog tag number and this changes year to year. Sometimes the owners move, so their address can change year to year. License number is unique to each dog as it's entered into the system. — user1424532, Jan 07 '20 at 20:37

score 1 · Answer 1 · answered Jan 08 '20 at 00:30

I tested the SQL script below with some mock data I created and it worked. I also created a view. I used a UNION ALL to retrieve all the data and then used a window function to filter the duplicates in the outer WHERE. I partitioned by the LicenseNumber since it is unique to the animal.

It is not the prettiest but it will get the data you need until you can come up with a cleaner solution.

SELECT LicenseYear
, [License Number] AS [LicenseNumber], [Last Name] AS [LastName], [First Name] AS 
[Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], 
[Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name] AS [DogName], [Dog 
Breed] AS [Breed], [Purchased] AS [Year], [Sex], [Altered], [Color], [Vet] AS 
[Veterinarian], [RabieTag] AS [RabiesTagNumber], [RabiesTag_Exp] AS 
[RabiesTagExpiration], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]

FROM  (
      SELECT ROW_NUMBER() OVER (PARTITION BY LicenseNumber ORDER BY LicenseNumber, LicenseYear DESC ) AS 'RowNum'
      , *
      FROM  (
            --SELECT 2020 AS 'LicenseYear'
            --, [License Number] AS [LicenseNumber], [Last Name] AS [LastName], [First Name] AS [Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], [Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name] AS [DogName], [Dog Breed] AS [Breed], [Purchased] AS [Year], [Sex], [Altered], [Color], [Vet] AS [Veterinarian], [RabieTag] AS [RabiesTagNumber], [RabiesTag_Exp] AS [RabiesTagExpiration], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]
            --FROM [dbo].[Dog_Info_2020]
            --WHERE Flag is not null

            --UNION ALL

            SELECT 2019 AS 'LicenseYear'
            , [License Number] AS [LicenseNumber], [Last Name] AS [LastName], [First Name] AS [Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], [Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name] AS [DogName], [Dog Breed] AS [Breed], [Purchased] AS [Year], [Sex], [Altered], [Color], [Vet] AS [Veterinarian], [RabieTag] AS [RabiesTagNumber], [RabiesTag_Exp] AS [RabiesTagExpiration], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]
            FROM [dbo].[Dog_Info_2019]
            WHERE Flag is not null

            UNION ALL

            SELECT 2018 AS 'LicenseYear'
            , [License Number] AS [LicenseNumber], [Last Name] AS [LastName], [First Name] AS [Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], [Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name] AS [DogName], [Dog Breed] AS [Breed], [Purchased] AS [Year], [Sex], [Altered], [Color], [Vet] AS [Veterinarian], [RabieTag] AS [RabiesTagNumber], [RabiesTag_Exp] AS [RabiesTagExpiration], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]
            FROM [dbo].[Dog_Info_2018] 
            WHERE Flag is not null
            ) Data
     ) Data 
WHERE RowNum = 1

Derrick Moeller · Answer 2 · 2020-01-07T21:14:20.077

Per your comments, License Number is a unique identifier therefore you can do this with a FULL JOIN.

SELECT COALESCE(di1.[License Number], di2.[License Number]) AS [LicenseNumber], ...
FROM Dog_Info_2019 di1
FULL JOIN Dog_Info_2018 di2 ON di2.[License Number] = di1.[License Number] AND di2.[Flag] IS NOT NULL AND di1.[Flag] IS NOT NULL
WHERE COALESCE(di1.[Flag], di2.[Flag]) IS NOT NULL 
ORDER BY COALESCE(di1.[License Number], di2.[License Number])

If you can also count on the Flag column to match for both records you can simplify the JOIN as so:

FULL JOIN Dog_Info_2018 di2 ON di2.[License Number] = di1.[License Number] AND di2.[Flag] = di1.[Flag]

score 0 · Answer 3 · answered Jan 07 '20 at 21:35

I assumed that Year is an integer in your example. Try this code.


SELECT [LicenseNumber], MAX([Year]) AS [Year]
INTO #temp
FROM    (
    SELECT [License Number] AS [LicenseNumber], [Purchased] AS [Year]
    FROM [dbo].[Dog_Info_2019]
    WHERE Flag is not null
    UNION
    SELECT [License Number] AS [LicenseNumber], [Purchased] AS [Year]
    FROM [dbo].[Dog_Info_2018] 
    WHERE Flag is not null
)
GROUP BY [LicenseNumber]

SELECT a.*
FROM (
    SELECT [License Number] AS [LicenseNumber], [Last Name] AS [LastName], [First Name] AS [Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], [Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name] AS [DogName], [Dog Breed] AS [Breed], [Purchased] AS [Year], [Sex], [Altered], [Color], [Vet] AS [Veterinarian], [RabieTag] AS [RabiesTagNumber], [RabiesTag_Exp] AS [RabiesTagExpiration], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]
FROM [dbo].[Dog_Info_2019]
WHERE Flag is not null

UNION

SELECT [License Number] AS [LicenseNumber], [Last Name] AS [LastName], [First Name] AS [Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], [Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name] AS [DogName], [Dog Breed] AS [Breed], [Purchased] AS [Year], [Sex], [Altered], [Color], [Vet] AS [Veterinarian], [RabieTag] AS [RabiesTagNumber], [RabiesTag_Exp] AS [RabiesTagExpiration], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]
FROM [dbo].[Dog_Info_2018] 
WHERE Flag is not null
) a
INNER JOIN #temp b
 ON a.[LicenseNumber] = b.[LicenseNumber]
 AND a.[Year] = b.[Year]

score 0 · Answer 4 · answered Jan 08 '20 at 20:08

Thank you all for your response; they helped out greatly.

We were able to get something working by using the following query to create a view which works properly.

SELECT DISTINCT 
         [Year] AS [LicenseYear], [License Number], [Last Name] AS [LastName], [First Name] AS [Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], [Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name], [Dog reed], [Purchased], [Sex], [Altered], [Color], [Vet], [RabieTag], [RabiesTag_Exp], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]
FROM [dbo].[Dog_Info_2019]
WHERE [Flag] IS NOT NULL
UNION
SELECT DISTINCT 
         [Year] AS [LicenseYear], [License Number], [Last Name] AS [LastName], [First Name] AS [Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], [Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name], [Dog Breed], [Purchased], [Sex], [Altered], [Color], [Vet], [RabieTag], [RabiesTag_Exp], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]
FROM [dbo].[Dog_Info_2018]
WHERE [Flag] IS NOT NULL 
         AND [Dog_Info_2018].[License Number] NOT IN 
         (SELECT [License Number] FROM [Dog_Info_2019])

score -1 · Answer 5 · answered Jan 07 '20 at 20:11

Try this:

SELECT TOP 1 * FROM (

    SELECT [License Number] AS [LicenseNumber], [Last Name] AS [LastName], [First Name] AS [Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], [Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name] AS [DogName], [Dog Breed] AS [Breed], [Purchased] AS [Year], [Sex], [Altered], [Color], [Vet] AS [Veterinarian], [RabieTag] AS [RabiesTagNumber], [RabiesTag_Exp] AS [RabiesTagExpiration], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]
    FROM [dbo].[Dog_Info_2019]
    WHERE Flag is not null
    UNION ALL
    SELECT [License Number] AS [LicenseNumber], [Last Name] AS [LastName], [First Name] AS [Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], [Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name] AS [DogName], [Dog Breed] AS [Breed], [Purchased] AS [Year], [Sex], [Altered], [Color], [Vet] AS [Veterinarian], [RabieTag] AS [RabiesTagNumber], [RabiesTag_Exp] AS [RabiesTagExpiration], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]
    FROM [dbo].[Dog_Info_2018] 
    WHERE Flag is not null

) AS Registrations
ORDER BY 
    Registrations.LicenseNumber;

If you need the most current entry then it'll be better to include a date (DESC) in your sort.

Thank you for your response. This won't work as I need to put this into a view for which I'll then need to queried for the full record information by the license number. This current write-up randomly selects a record and not the record I'm looking for. — user1424532, Jan 07 '20 at 20:34
Right, as this example is going off your own--which was also not restricted. To restrict your results to a specific license number, add a WHERE clause prior to the ORDER BY doing so. — critical_error, Jan 08 '20 at 18:57

Two tables, distinct license number to return

5 Answers5