How to remove duplicate columns from join in SQL

Question

I have the following code

SELECT * 
FROM customer
INNER JOIN
    (SELECT   
         customerid, newspapername, enddate, n.publishedby 
     FROM
         newspapersubscription ns, newspaper n 
     WHERE 
         publishedby IN (SELECT publishedby 
                         FROM newspaper 
                         WHERE ns.newspapername = n.NewspaperName)
UNION

SELECT
    customerid, Magazinename, enddate, m.publishedby 
FROM
    magazinesubscription ms, magazine m 
WHERE
    publishedby IN (SELECT publishedby 
                    FROM magazine 
                    WHERE ms.Magazinename = m.MagazineName)) ON customer.customerid = customerid
ORDER BY 
    customer.customerid;

The customer table has the following:

 customerid | customername | customersaddress

This query returns the following result:

customerid | customername | customersaddress | customerid | newspapername | enddate| publishedby

What I actually want is

customerid | customername | customersaddress | newspapername | magazinename | enddate| publishedby

Here, the newspapername field should be blank if the magazinename is present and vice versa. Also, the duplicate field of customerid from the union operations should not be present, while in my result, the value of both the newspapername and the magazinename are put under newspapername title.

How can I do that?

Answer 1

Since you are querying the table with '*', you will always get all the columns in both tables. In order to omit this column, you will have to manually name all columns you DO want to query. To address your other need, you need to simply insert a dummy column to each clause in the union query. Below is an example that should work to allow for what you want -

SELECT customer.customerid, customer.customername, customer.customeraddress, newspapername, magazinename, enddate, publishedby 
FROM customer
INNER JOIN
(select  customerid, newspapername, null Magazinename, enddate, n.publishedby 
 from newspapersubscription ns, newspaper n 
 where publishedby in(select publishedby 
                    from newspaper 
                    where ns.newspapername = n.NewspaperName)
UNION
select  customerid, null newspapername, Magazinename, enddate, m.publishedby 
from magazinesubscription ms, magazine m 
 where publishedby in(select publishedby 
                    from magazine 
                     where ms.Magazinename = m.MagazineName))
on customer.customerid = customerid
ORDER BY customer.customerid;

Answer 2

To get the projection you want, build sub-queries of the right shape and UNION them to get the result set. UNION ALL is better than UNION because it avoids a sort: you know you'll get a distinct set because you're joining on two different tables.

select * from (
    select customer.* 
           , n.newspapername
           , null as magazinename
           , ns.enddate
          , n.publishedby 
    from customer
        join newspapersubscription ns 
            on ns.customerid = customer.customerid
        join newspaper n
            on  n.newspapername = ns.newspapername 
    union all
    select customer.* 
           , null as newspapername
           , m.magazinename
           , ms.enddate
           , m.publishedby 
    from customer
        join magazinesubscription  ms 
            on ms.customerid = customer.customerid
        join magazine m
            on  m.magazinename = ms.magazinename 
            )
order by customerid, newspapername nulls last, magazinename ;

Here is the output from my toy data set (which lacks publishedby columns:

CUSTOMERID CUSTOMERNAME         NEWSPAPERNAME          MAGAZINENAME           ENDDATE
---------- -------------------- ---------------------- ---------------------- ---------
        10 DAISY-HEAD MAISIE    THE DAILY BUGLE                               30-SEP-17
        30 FOX-IN-SOCKS         THE DAILY BUGLE                               30-SEP-17
        30 FOX-IN-SOCKS         THE WHOVILLE TIMES                            30-SEP-16
        30 FOX-IN-SOCKS                                GREEN NEWS             31-DEC-17
        30 FOX-IN-SOCKS                                TWEETLE BEETLE MONTHLY 31-DEC-16
        40 THE LORAX                                   GREEN NEWS             31-DEC-18

6 rows selected.

SQL>