CODEWITHSUNDEEP
sqlselectinner-joinunion
RevXaisks
RevXaisks

Reputation: 183

Adding an additional column to SQL UNION SELECT

Suppose I have the following mapped and normalized tables;

Group                       User                        Contact                     BelongsTo       
+-------+------+------+     +-------+------+------+     +-------+------+------+     +-------+------+
|    gID|  name|  col3|     |    uID| fname| sname|     |    cID|  name|  col3|     |  accID|   gID|
+-------+------+------+     +-------+------+------+     +-------+------+------+     +-------+------+
|1      |ABC   |?     |     |1      |JJ    |BB    |     |4      |ABCD  |?     |     |1      |2     |
+-------+------+------+     +-------+------+------+     +-------+------+------+     +-------+------+
|2      |BCD   |?     |     |2      |CC    |LL    |     |5      |BCDE  |?     |     |3      |2     |
+-------+------+------+     +-------+------+------+     +-------+------+------+     +-------+------+
|3      |DEF   |?     |     |3      |RR    |NN    |     |6      |CDEF  |?     |     |5      |3     |
+-------+------+------+     +-------+------+------+     +-------+------+------+     +-------+------+

Using EERM, User and Contact are subclasses of "Account" superclass. (not shown) An account can belong to many groups, thus "BelongsTo" table records the M:N relationship between the Accounts and Group membership.

I would like an SQL statement which will allow me to query all the users and contacts that have membership in a Group matching conditions as follows:

SELECT
    tc."cID" AS "accID",
    tc."name" AS "accName",
    tbt."gID"
FROM "tblContact" tc
    INNER JOIN "tblBelongsTo" tbt
        ON tbt."accID" = tc."cID"
UNION SELECT
    tu."uID" AS "accID",
    CONCAT (tu."fname", ' ', tu."sname") AS "accName",
    tbt."gID"
FROM "tblUser" tu
    INNER JOIN "tblBelongsTo" tbt
        ON tbt."accID" = tu."uID"
ORDER BY "accID" ASC;

The above works, I have combined UNION SELECT in the query as the number of columns match either side when I CONCAT the forename and surname together. Resulting in a global "account_name" & "account_id" column.

My question is this: How would I go about adding an extra column so that I can see what the group name is?

ie from this:

Result
+-------+-------+------+
|  accID|accName|   gID|
+-------+-------+------+
|1      |JJBB   |2     |
+-------+-------+------+
|3      |RRNN   |2     |
+-------+-------+------+
|5      |BCDE   |3     |
+-------+-------+------+

to this:

Result (2)
+-------+-------+------+------+
|  accID|accName|   gID|  name|
+-------+-------+------+------+
|1      |JJBB   |2     |   BCD|
+-------+-------+------+------+
|3      |RRNN   |2     |   BCD|
+-------+-------+------+------+
|5      |BCDE   |3     |   DEF|
+-------+-------+------+------+

It seems everything I have tried causes the UNION SELECT to break (because of unmatched column). Likewise, I had little luck in combining sub-queries. I am probably missing something very obvious...

Thanks in advance.

Upvotes: 0

Views: 3381

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can join it to the group table to get the name.

select x.*, g.name
from group g join
(
SELECT
tc."cID" AS "accID",
tc."name" AS "accName",
tbt."gID"
FROM "tblContact" tc
INNER JOIN "tblBelongsTo" tbt
ON tbt."accID" = tc."cID"
UNION 
SELECT
tu."uID" AS "accID",
CONCAT (tu."fname", ' ', tu."sname") AS "accName",
tbt."gID"
FROM "tblUser" tu
INNER JOIN "tblBelongsTo" tbt
ON tbt."accID" = tu."uID"
) x on x.gid = g.gid
order by accid;

Upvotes: 1

Related Questions