Combining 4 tables using Union All and Group by ID and Description

Question

I want to combine 4 tables with non-homogeneous fields and group by ID and DESCRIPTION and place all the uniform values in the same column as shown on the picture. I was able to query the union all but I can't move forward to grouping. How can I do this?

SELECT ID, USERNAME, LOGIN_ID, DESCRIPTION, RIGHT1, RIGHT2, RIGHT3, RIGHT4, '1A' ENTITY
FROM tbla
UNION ALL
SELECT ID, USERNAME, LOGIN_ID, DESCRIPTION, RIGHT1, '' RIGHT2, '' RIGHT3, '' RIGHT4, '1G' ENTITY
FROM tblb
UNION ALL
SELECT ID, USERNAME, LOGIN_ID, DESCRIPTION, RIGHT1, '' RIGHT2, '' RIGHT3, '' RIGHT4, '1S' ENTITY
FROM tblc
UNION ALL
SELECT ID, USERNAME, LOGIN_ID, DESCRIPTION, RIGHT1, '' RIGHT2, '' RIGHT3, '' RIGHT4, '1P' ENTITY
FROM tbld

Please show us the data you are starting from rather than the result from your existing query. Also, please provide your data as tabular text rather than as images. — GMB, Oct 24 '20 at 17:24
Hi @GMB this is actually related to the previous query you have made earlier, I’ve tried to include all the fields for each table but I was getting error “the fields are fewer than...” something like that. Ref: https://stackoverflow.com/questions/64508233/combining-values-from-multiple-tables-using-join-clause-with-multiple-on/64508259?noredirect=1#comment114066382_64508259 — Thirdy Leon, Oct 24 '20 at 17:34

score 0 · Answer 1 · edited Oct 24 '20 at 19:18

You can create a result set using these four tables and then apply the group by ID and DESCRIPTION as below:

SELECT
*
FROM 
(
    SELECT ID, USERNAME, LOGIN_ID, DESCRIPTION, RIGHT1, RIGHT2, RIGHT3, RIGHT4, '1A' ENTITY
    FROM tbla
    UNION ALL
    SELECT ID, USERNAME, LOGIN_ID, DESCRIPTION, RIGHT1, '' RIGHT2, '' RIGHT3, '' RIGHT4, '1G' ENTITY
    FROM tbla
    UNION ALL
    SELECT 1ID, USERNAME, LOGIN_ID, DESCRIPTION, RIGHT1, '' RIGHT2, '' RIGHT3, '' RIGHT4, '1S' ENTITY
) Result 
GROUP BY Result.DESCRIPTION, Result.ID

Combining 4 tables using Union All and Group by ID and Description

1 Answers1