UNION to specific field

Question

Can somebody help me to change my SQL query so, that the "UNION" statement will only be applied to the fields "acronym" and "user_pk" (and not also to the fields "surname, givename; lastname, firstname")? But in the "parent" "SELECT" statement the fields "acronym", "surname" and "givename" should still exist.

SELECT acronym, surname, givename
FROM
(
    SELECT acronym, surname, givename from table_1
    UNION
    SELECT user_pk, lastname, firstname  from table_2
)

Thanks for your support!

Kind regards, shub

Answer 1

If the same string appears as both an acronym in Table_1 and a user_pk in Table_2, is it fair to guess that if one of the names is wholly null, you don't want to see it, but if either of the name fields in either row is not null, you'd rather see two rows than just one row?

Working on that assumption:

SELECT t1.acronym, t1.surname, t1.givename
  FROM table_1 AS t1
 WHERE t1.surname IS NOT NULL OR t1.givename IS NOT NULL
UNION
SELECT t2.user_pk AS acronym, t2.lastname AS surname, t2.firstname AS givename
  FROM table_2 AS t2
 WHERE t2.lastname IS NOT NULL OR t2.firstname IS NOT NULL

---

If this is not close enough to what you wanted, please give the required output from the following tables:

Table_1
Acronym          Surname      Givename
Denton Powell     Powell        Denton
Jane Goodall     Goodall
Susan Mitchell                   Susan
Martin Catcall
John Thimble     Thimble          John

Table_2
User_pk        Lastname      Firstname
Denton Powell
Jane Goodall                      Jane
Susan Mitchel   Mitchell
Martin Catcall   CatCall         Marty
John Thimble      Needle         David

There are more possible combinations, but by the time you've specified what you want for those, you'll have covered most cases in sufficient detail that the others should be obvious.

---

Answering comment to main question

I want to see only one row, because this "user_pk" exists already in table_1 (in field "acronym"). In your case the output should be: Denton Powell; Powell; Denton. The row in the table_2 should be ignored.

Interpreting again, this means that if the entry appears in Table_1, use it; only use the entry from Table_2 if there is no matching entry in Table_2?

Again, working on that assumption, then you want the union of the data from Table_1 with the 'complement of the semi-join of Table_2 with Table_1', which is a fancy-pants way of saying 'the rows in Table_2 that don't have an entry in Table_1':

SELECT t1.acronym, t1.surname, t1.givename
  FROM table_1 AS t1
UNION
SELECT t2.user_pk AS acronym, t2.lastname AS surname, t2.firstname AS givename
  FROM table_2 AS t2
 WHERE t2.user_pk NOT IN (SELECT t1a.acronym FROM table_1 AS t1a);

This loses the conditions on null-ness in the names. If some user has an entry in Table_1 with no information in the surname and given name fields, then you'll see those empty names. You'll have to tweak things a bit if there are other conditions that you want applied.

Incidentally, I don't think you ever specified that 'user_pk' is a primary key (non-null and unique), nor that 'acronym' is a primary key. Such details can be helpful, and would eliminate some worries for people answering your question (even if they haven't been voiced worries). It also reassures us that you know what you're talking about.