I will start out by saying, I am working with two tables, that are a bit of a mess in terms of how they are organized. I am currently pleading my case to get this adjusted, but have no control over them. So the organization of the tables is out of my control.
I have one table that looks like this
Customer
| id | name | date_joined |
|----|------|-------------|
| 1 | Bob | 2012-01-01 |
| 2 | Jack | 2012-01-01 |
| 3 | Jill | 2012-01-01 |
Since the customer table was created, another table has been created to hold customer information
Customer2
| id | name | year_joined | month_joined | day_joined |
|----|---------|-------------|--------------|------------|
| 4 | Ken | 2013 | 1 | 1 |
| 5 | Lindsey | 2013 | 1 | 1 |
| 1 | Bob | 2012 | 1 | 1 |
You will notice that while Customer2 contains new customers for the first two entries it also contains Bob, who is the same bob that is in the first table with updated information to match the new Customer2 table layout.
I need to select all of these records into one result set based on a list of ID's, it is assumed that the ID's are unique. Therefore we can be sure that Bob in Customer is the same as Bob in Customer2. I am currently doing this with a union statement like this.
select *
from (
select id,
name,
date_joined,
'' as year_joined,
'' as month_joined,
'' as day_joined
from customer
union
select id,
name,
'' as date_joined,
year_joined,
month_joined,
day_joined
from customer2 )
as U where U.id in (list of ID's)
However this results in a table that looks like this
| id | name | date_joined | year_joined | month_joined | day_joined |
|----|---------|-------------|-------------|--------------|------------|
| 4 | Ken | | 2013 | 1 | 1 |
| 5 | Lindsey | | 2013 | 1 | 1 |
| 1 | Bob | | 2012 | 1 | 1 |
| 1 | Bob | 2012-01-01 | | | |
| 2 | Jack | 2012-01-01 | | | |
| 3 | Jill | 2012-01-01 | | | |
As we can see we have 'duplicate' records for Bob, which are not really duplicates. My question is this: When I encounter 'duplicates' like this, is there any way I can specify to only pick the record from the Customer2 table? I need this because I do not want duplicates and prefer to keep the record that matches the latest table layout.
This should get you started:
SELECT
id
, name
, date_joined
, NULL year_joined
, NULL month_joined
, NULL day_joined
FROM Customer
WHERE id NOT IN (SELECT id FROM Customer2)
UNION ALL
SELECT
id
, name
, NULL
, year_joined
, month_joined
, day_joined
FROM Customer2
ORDER BY id
;
Alternatively, using just SET operations:
(SELECT
id
, name
, date_joined
, NULL year_joined
, NULL month_joined
, NULL day_joined
FROM Customer
EXCEPT
(SELECT
id
, name
, CONVERT(DATE,
CAST(year_joined AS VARCHAR(4)) + '-'
+ CAST(month_joined AS VARCHAR(2)) + '-'
+ CAST(day_joined AS VARCHAR(2))
, 102
)
, NULL
, NULL
, NULL
FROM Customer2
)
)
UNION ALL
SELECT
id
, name
, NULL
, year_joined
, month_joined
, day_joined
FROM Customer2
ORDER BY id
;
Either one takes from the Customer those, not found in Customer2, and combines them with all found in Customer2:
| id | name | date_joined | year_joined | month_joined | day_joined |
|----|---------|-------------|-------------|--------------|------------|
| 1 | Bob | (null) | 2012 | 1 | 1 |
| 2 | Jack | 2012-01-01 | (null) | (null) | (null) |
| 3 | Jill | 2012-01-01 | (null) | (null) | (null) |
| 4 | Ken | (null) | 2013 | 1 | 1 |
| 5 | Lindsey | (null) | 2013 | 1 | 1 |
See it in action: SQL Fiddle.
Myself though, I'd usually prefer a genuine date column over three columns with date particles...
Please comment, if and as this requires adjustment / further detail.