CODEWITHSUNDEEP
sqloracle-databaset-sqloracle11goracle10g
darkredcaffeine
darkredcaffeine

Reputation: 11

SQL join - duplicate rows

I have three tables (simplified version - the whole picture is a bit more complex).

TABLE: CUSTOMER    TABLE: PURCHASE1           TABLE: PURCHASE2
===============    =======================    =======================
CustomerID         CustomerID  | ProductID    CustomerID  | ProductID
---------------    ------------|----------    ------------|----------
1                  1           | 51            1          | 81
2                  1           | 52            1          | 82
3                  2           | 52            1          | 83

I know the table structure isn't the best but that's not what I need help with. The products held in the purchase tables are of different types, if that helps to provide context.

I'm trying to join the tables, using a query like this:

Select 
    customer.customerid, purchase1.productid as P1, 
    purchase2.productid as P2
From  
    customer
Left join 
    purchase1 on customer.customerid = purchase1.customerid
Left join 
    purchase2 on customer.customerid = purchase2.customerid
Where 
    customer.customerid = 1;

This produces the following:

CustomerID | P1 | P2
--------------------
1          | 51 | 81
1          | 51 | 82
1          | 51 | 83
1          | 52 | 81
1          | 52 | 82
1          | 52 | 83

How do I get it to do this instead?

CustomerID | P1   | P2
-----------|------|---
1          | 51   | null
1          | 52   | null
1          | null | 81
1          | null | 82
1          | null | 83

The first table has a row for every combination of P1 and P2. The second table only has a row for each customer-product combination.

Can I do this without using UNION? The reason I ask, is that because the query will become more complex, using columns from other rows that aren't in PURCHASE1 or PURCHASE2.

If I have to use UNION, how can I do it such that I can still select from other tables and have additional columns in my query?

Upvotes: 1

Views: 267

Answers (5)

Boneist
Boneist

Reputation: 23588

I would first of all union up all the tables and then join them to the customer table - like so:

with customer as (select 1 customerid, 'bob' name from dual union all
                  select 2 customerid, 'ted' name from dual union all
                  select 3 customerid, 'joe' name from dual),
    purchase1 as (select 1 customerid, 51 productid from dual union all
                  select 1 customerid, 52 productid from dual union all
                  select 2 customerid, 52 productid from dual),
    purchase2 as (select 1 customerid, 81 productid from dual union all
                  select 1 customerid, 82 productid from dual union all
                  select 1 customerid, 83 productid from dual),
    -- end of mimicking your table and data; main query is below:
    purchases as (select customerid, productid productid1, null productid2
                  from   purchase1
                  union all
                  select customerid, null productid1, productid productid2
                  from   purchase2)
select c.customerid,
       c.name,
       p.productid1,
       p.productid2
from   customer c
       inner join purchases p on (c.customerid = p.customerid)
order by c.customerid,
         p.productid1,
         p.productid2;

CUSTOMERID NAME PRODUCTID1 PRODUCTID2
---------- ---- ---------- ----------
         1 bob          51           
         1 bob          52           
         1 bob                     81
         1 bob                     82
         1 bob                     83
         2 ted          52

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

I would do it this way:

select customerid, p1, p2
  from customer
  left join (
    select customerid, productid p1, null p2 from purchase1
    union all
    select customerid, null p1, productid p2 from purchase2
    ) using (customerid)
  where customerid = 1;

SQLFiddle demo

Now you can attach rest of tables without repeated logic.

Upvotes: 1

Sam CD
Sam CD

Reputation: 2097

This uses Union, but in a slightly different way, within subqueries, which might provide you more flexibility.

select distinct t1.pID,t2.pID
from (select ID,pID from Puchase1
        union all
        select ID, null from Purchase1) t1
right join (select ID,pID from Purchase2
            union all
            select ID, null from Purchase2) t2
on t1.ID = t2.ID
where t1.ID = 1
and (t1.pID is not null or t2.pID is not null)
and (t1.pID is null or t2.pID is null)

Upvotes: 0

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8517

Use Union . See DEMO. In union, you have to have same number of columns in both queries so use NULL to match number of column in both query

Select * from (Select customer.customerid, purchase1.productid as P1, NULL as P2
from customer
INNER join purchase1
on customer.customerid = purchase1.customerid

UNION ALL

Select customer.customerid, NULL as P1, purchase2.productid as P2
from customer 
INNER join purchase2
on customer.customerid = purchase2.customerid) tb
where tb.customerid = 1;

Upvotes: 4

Liss
Liss

Reputation: 441

It's probably easiest to just change it to a union query like this.

select customer.customerid, purchase1.productid as P1, null as P2
from customer
left join purchase1
on customer.customerid = purchase1.customerid
union all
select customer.customerid, null as P1, purchase2.productid as P2
from customer
left join purchase2
on customer.customerid = purchase2.customerid
where customer.customerid = 1;

Upvotes: 0

Related Questions