I have an ID table which links out to a name table and also links out to an address table. The name table contains all of the name changes of a customer through time using effective_dates. The address table does the same thing using effective_dates. In other words, we never delete or update any records in the name or address table; just add.

This is a sample SQL:

SELECT a.*, NVL(b.lname, ' ') lname,
NVL(b.fname, ' ') fname, NVL(b.mname, ' ') mname,
NVL(c.city, ' ') city, NVL(c.street, ' ') street...
FROM id_table a LEFT JOIN name_tbl b
ON a.id = b.id
LEFT JOIN address_tbl c
ON a.id = b.id
WHERE TTOC(b.effective_date, 1) =
(SELECT MAX(TTOC(x.effective_date, 1)) FROM name_tbl x WHERE x.id = b.id) AND
TTOC(c.effective_date, 1) =
(SELECT MAX(TTOC(y.effective_date, 1)) FROM address_tbl y WHERE y.id = c.id)

This effectively works out to an inner join anyway because of the where clause. That means that if an id does not have a name or address applied to it, the record is ignored.

That gets us to the union.

SELECT a.*, b.lname, b.fname, b.mname, c.city, c.street...
FROM id_table a INNER JOIN name_tbl b
ON a.id = b.id
INNER JOIN address_tbl c
ON a.id = b.id
WHERE TTOC(b.effective_date, 1) =
(SELECT MAX(TTOC(x.effective_date, 1)) FROM name_tbl x WHERE x.id = b.id) AND
TTOC(c.effective_date, 1) =
(SELECT MAX(TTOC(y.effective_date, 1)) FROM address_tbl y WHERE y.id = c.id)
UNION
SELECT a.*, NVL(b.lname, ' ') lname,
NVL(b.fname, ' ') fname, NVL(b.mname, ' ') mname,
NVL(c.city, ' ') city, NVL(c.street, ' ') street...
FROM id_table a LEFT JOIN name_tbl b
ON a.id = b.id
LEFT JOIN address_tbl c
ON a.id = b.id
WHERE TTOC(b.effective_date, 1) NOT IN
(SELECT TTOC(x.effective_date, 1) FROM name_tbl x WHERE x.id = b.id) AND
TTOC(c.effective_date, 1) NOT IN
(SELECT TTOC(y.effective_date, 1) FROM address_tbl y WHERE y.id = c.id)

This example will get us all records that have both (name and address) and just the id_table with no (name and address). Now... what if just the name is missing or just the address is missing? Sounds like I do 2 more unions. Is there a better solution? Keep in mind that I need the most recent NAME and ADDRESS which belongs to the ID.