Problem with Duplicates in UNION query

[txdave35]

Hey guys,

I have a query that looks for people who are in a certain age range. The query does this by comparing their birth date to the current date. I discovered today the the query is not picking up some people because their birth date is stored in a separate table.

I thought I could do a simple UNION query with the same statements, but looking at the different table. Then I learned that they may have a birthdate in both tables. This would cause a duplicate record in my query results if I try to use UNION.

My query looks something like this:

select name from table anty
where YEAR(CURRENT DATE) - YEAR(BIRTH_DT) between 18 and 20

UNION ALL

select name from table mbr

where YEAR(CURRENT DATE) - YEAR(MBR_BIRTH_DT) between 18 and 20

So how can I get the query to examine both tables for matches while avoiding duplication if they have a birth date in both tables?

 

[gmmastros]

Union All will return duplicates. Remove the "All" part, and your query should work the way you want it to.

select name from table anty
where YEAR(CURRENT DATE) - YEAR(BIRTH_DT) between 18 and 20

UNION

select name from table mbr

where YEAR(CURRENT DATE) - YEAR(MBR_BIRTH_DT) between 18 and 20

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[txdave35]

Thanks George. I wasn't aware of that!