Add 2nd column to union query

This union query takes email addresses from various fields and sources and presents them as a single column of data (no duplicates). I would like a second column to come up, containing the CIFM_NBR field which is present in the 4_QRY_Final query. How do I modify to get to that?

SELECT CIFM_EMLHP_ADDR_502 FROM 4_QRY_Final
UNION SELECT CIFM_EMLHP_ADDR_505 FROM 4_QRY_Final
WHERE CIFM_EMLHP_ADDR_505 IS NOT NULL
UNION SELECT CIFM_EMLHP_ADDR_508 FROM 4_QRY_Final
WHERE CIFM_EMLHP_ADDR_508 IS NOT NULL
UNION SELECT CIFM_EMLHP_ADDR_511 FROM 4_QRY_Final
WHERE CIFM_EMLHP_ADDR_511 IS NOT NULL
UNION SELECT CIFM_EMLHP_ADDR_514 FROM 4_QRY_Final
WHERE CIFM_EMLHP_ADDR_514 IS NOT NULL
UNION SELECT CIFM_EMLHP_ADDR_517 FROM 4_QRY_Final
WHERE CIFM_EMLHP_ADDR_517 IS NOT NULL
UNION SELECT EmailAddress FROM 4_QRY_Final
WHERE EmailAddress IS NOT NULL
UNION SELECT EmailAddress FROM _20140128_ABCStaff
UNION SELECT EmailAddress FROM _20140128_DEFStaff;
K_DeutschAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
just add the column to the select statement

SELECT CIFM_EMLHP_ADDR_502, CIFM_NBR FROM 4_QRY_Final
UNION
SELECT CIFM_EMLHP_ADDR_505, CIFM_NBR FROM 4_QRY_Final
WHERE CIFM_EMLHP_ADDR_505 IS NOT NULL

... add the other lines here for 4_QRY_Final

UNION
SELECT EmailAddress, Null as CIFM_NBR FROM _20140128_ABCStaff
UNION
SELECT EmailAddress, Null as CIFM_NBR  FROM _20140128_DEFStaff;
0
 
K_DeutschAuthor Commented:
almost there...the same email address can be present on multiple CIFM_NBR, so the query now produces duplicate email addresses that I want to eliminate
0
 
Dale FyeCommented:
Then you need to determine which CIFM_NBR (first, last, most frequent) you want from each email.

You could wrap the whole thing in a Subquery, something like:

SELECT Temp.EmailAddress, MIN(temp.CIFM_NBR) as CIFM_NBR
FROM (
SELECT ..
UNION
SELECT ..
...
) as Temp
GROUP BY temp.EmailAddress
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.