How to use UNION, GROUP and ORDER BY in SQL query?

Question

I have for Tables: List_of_payment, payment_tbl, foods_tbl, drinks_tbl

List_of_payment:

bill_id | year  | owner_ID 

101     | 2002  | 014

101     | 2002  | 014

101     | 2003  | 014

101     | 2003  | 014

101     | 2004  | 014

101     | 2005  | 014

payment_tbl:

bill_id | prod_foods_id | prod_drinks_id | prod_schooL_supply

101     |  123          |                |     

101     |               |     001        |    

101     |               |                |     234      

foods_tbl:

foods_id | name

123       | chocolate

021       | biscuit

312       | junk foods

drinks_tbl:

drinks_id | name

000       | coke

001       | juice

002       | milk

skol_supply_tbl:

supply_id | name

258       | pen

234       | pencil

987       | eraser

my question is how to use "UNION" in order to dispaly value of year and (either name of foods,drinks,skol supply) when I enter (either foods_id,drinks_id,supply_id)

below is my code:

value of "PK_ID" is either = 123,001,234 depends on the user

select payment_tbl.prod_foods_id,List_of_payment.year from payment_tbl 
join List_of_payment on payment_tbl.bill_id = List_of_payment.bill_id
where year in  (select year from List_of_payment where owner_id ='PK_ID') group by payment_tbl.prod_foods_id,List_of_payment.year order by List_of_payment.year 

Unioin

select payment_tbl.prod_drinks_id,List_of_payment.year from payment_tbl 
join List_of_payment on payment_tbl.bill_id = List_of_payment.bill_id
where year in  (select year from List_of_payment where owner_id ='PK_ID') group by payment_tbl.prod_drinks_id,List_of_payment.year order by List_of_payment.year 

UNion 

select payment_tbl.prod_schooL_supply,List_of_payment.year from payment_tbl 
join List_of_payment on payment_tbl.bill_id = List_of_payment.bill_id
where year in  (select year from List_of_payment where owner_id ='PK_ID') group by payment_tbl.prod_schooL_supply,List_of_payment.year order by List_of_payment.year 

igot an error when i execute my query "ERROR: syntax error at or near "union""

thanks in advance

Answer 1

Try like this:

SELECT  tbl.*
FROM    ( SELECT    payment_tbl.prod_foods_id,
                    List_of_payment.year
          FROM      payment_tbl
                    JOIN List_of_payment ON payment_tbl.bill_id = List_of_payment.bill_id
          WHERE     year IN ( SELECT    year
                              FROM      List_of_payment
                              WHERE     owner_id = 'PK_ID' )
          UNION
          SELECT    payment_tbl.prod_drinks_id,
                    List_of_payment.year
          FROM      payment_tbl
                    JOIN List_of_payment ON payment_tbl.bill_id = List_of_payment.bill_id
          WHERE     year IN ( SELECT    year
                              FROM      List_of_payment
                              WHERE     owner_id = 'PK_ID' )
          UNION
          SELECT    payment_tbl.prod_schooL_supply,
                    List_of_payment.year
          FROM      payment_tbl
                    JOIN List_of_payment ON payment_tbl.bill_id = List_of_payment.bill_id
          WHERE     year IN ( SELECT    year
                              FROM      List_of_payment
                              WHERE     owner_id = 'PK_ID' )
        ) tbl
GROUP BY payment_tbl.prod_drinks_id,
        List_of_payment.YEAR
ORDER BY List_of_payment.year