Horizontal union select query

#1

Hi, I’m trying to perform a union query to join two tables together. This is the query I’ve tried so far:

SELECT km_expense_id, km_expense_description FROM km_expenses WHERE km_expense_building= 3 UNION ALL SELECT km_riparto_expense_id, km_riparto_expense_value FROM km_riparti_expenses WHERE km_riparto_building_id = 3 AND km_riparto_expense_riparto_id = 2;

and it returns:

+---------------+--------------------------+
| km_expense_id | km_expense_description   |
+---------------+--------------------------+
|            99 | Acqua                    |
|           100 | Assicurazione fabbricato |
|           101 | Cancelleria e stampati   |
|           102 | Compenso amministratore  |
|            99 | 0.12                     |
|           100 | 12.00                    |
|           101 | 1234.55                  |
+---------------+--------------------------+

How can I instead achieve the following?

+---------------+--------------------------+------------------+
| km_expense_id | km_expense_description   | km_expense_value |
+---------------+--------------------------+------------------+
|            99 | Acqua                    | 0.12             |
|           100 | Assicurazione fabbricato | 12.00            |
|           101 | Cancelleria e stampati   | 1234.55          |
|           102 | Compenso amministratore  | NULL             |
+---------------+--------------------------+------------------*
#2

why is km_riparto_expense_riparto_id = 2 in only the second SELECT and not the first?

#3

HI @r937 thanks for your help, it is because I don’t have that column in the table km_expenses

#4

okay, thanks, i get it now

SELECT km_expense_id
     , MAX(km_expense_description) AS km_expense_description
     , MAX(km_expense_value)       AS km_expense_value
  FROM ( SELECT km_expense_id
              , km_expense_description
              , NULL AS km_expense_value
           FROM km_expenses 
          WHERE km_expense_building= 3 
         UNION ALL 
         SELECT km_riparto_expense_id
              , NULL
              , km_riparto_expense_value 
           FROM km_riparti_expenses 
          WHERE km_riparto_building_id = 3 
            AND km_riparto_expense_riparto_id = 2;
       ) AS data
GROUP
    BY km_expense_id
1 Like
#5

wow @r937 it works fine, many many thanks.
just had to romve ; from km_riparto_expense_riparto_id = 2;

#6

sorry, copy pasta error

1 Like