Left outer join in MySQL

Question

Is it possible to join these two tables to get the following result.

Table Stats

Date         Cus_ID    Pur        Amount 
2015-02-01   2585711    2           100
2015-02-02   2585711    5           250
2015-05-03   2585711    8           400
2015-02-01   2585475    2           100
2015-02-02   2585475    5           250
2015-05-03   2585475    8           400

Table Customer     
Reg_Date     Cus_ID    Gender   Country
2014-04-11   2585711    Male    Sweden
2015-02-01   2585475    Female  Sweden

Expected Result

Date        Cus_ID   Pur       Amount   Gender  Country
2014-04-11  2585711  NULL       NULL    Male    Sweden
2015-02-01  2585711  2          100     Male    Sweden
2015-02-02  2585711  5          250     Male    Sweden
2015-05-03  2585711  8          400     Male    Sweden
2015-02-01  2585475  2          100     Female  Sweden
2015-02-02  2585475  5          250     Female  Sweden
2015-05-03  2585475  8          400     Female  Sweden

if I use a left outer join to join the two tables, I get the following result

Date        Cus_ID  Pur Amt Gender  Country
2015-02-01  2585711 2   100 Male    Sweden
2015-02-02  2585711 5   250 Male    Sweden
2015-05-03  2585711 8   400 Male    Sweden
2015-02-01  2585475 2   100 Female  Sweden
2015-02-02  2585475 5   250 Female  Sweden
2015-05-03  2585475 8   400 Female  Sweden

My query:

Select 
if(a.Date IS NULL,b.reg_date,a.date) as Date,
b.cus_id,
pur,
Amount,
gender,
country
     from 
        (Select * from stats) as a
                 Left outer join
                      (select * from customer) as b
on a.cus_id = b.cus_id

Any help will be highly appreciated. Thanks!

What's your criteria for adding the "null purchase" line for customer 2585711, but not customer 2585475? If you want "null purchase" line for every customers than use a standard left join and union it with a query to select all customer rows. — a'r
if(a.Date IS NULL,b.reg_date,a.date) as Date since you use a LEFT OUTER JOIN a.date is not going to be NULL (unless it's NULL in the stats table). In your Exepected Results where did that first record come from? Why is that record significant? — JNevill
I want to join the Customer and the stats table into one to create a single report where I could get the Total number of Registrants per day and the revenue generated per day. I will be creating a report in excel, using date as a Row label, Since customer 2585711 registered on the 11th of April and made his 1st purchase on the 2nd of Feb 2015, I need the 1st row with NULL values for my report to count him as a registrant for the 11th. 2585475 registered and purchased on the 1st of Feb, so will be counted as a registered member for the 1st. — Justus

Ben Ben · Accepted Answer · 2016-01-22T17:36:15

I don't know if this is still relevant.

If the idea to show every date a customer was active, even if they didn't make a purchase, I'd start with a table that gives me all dates and customer pairs first.

select [date],[cust_id] 
into #activity
from stats
union
select [reg_date],[cust_id]
from customer


select t1.*, t2.pur, t2.amt
from #activity t1
left outer join stats t2 on 
t1.[date] = t2.[date] and t1.[cust_id] = t2.[cust_id]

Left outer join in MySQL

1 Answers