evan evan - 7 months ago 7
SQL Question

How to remove duplicate columns from join in SQL

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

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;