SQL Left Join on Tables

Question

For these two Tables write a Query that returns the email from the CustomerOrders along with the name of the Event and Order Date of the LAST event that the Customer bought.

Event Table

---------+---------------------+
| EventID | EventName           |
+---------+---------------------+
|       1 | Rolling Stones      |
|       2 | Ike and Tina Turner |
|       3 | Lakers vs Clipper   |
|       4 | Disney on Ice       |
|       5 | Yankees Vs Red Sox  |
+---------+---------------------+

CustomerOrders Table

+------------------+-----------+------------+---------------------------+---------+
| idCustomerOrders | Name      | Date_order | Email                     | EventID |
+------------------+-----------+------------+---------------------------+---------+
|                1 | Paul      | 2022-02-02 | Paulryan@aol.com          |       1 |
|                2 | Max       | 2022-03-02 | Maxwellsmith@aol.com      |       2 |
|                3 | Jack      | 2022-02-02 | JacksonSmith@hotmail.com  |       5 |
|                4 | Alexander | 2022-05-02 | ALexDex@gmail.com         |       4 |
|                5 | Bobby     | 2022-06-02 | BobbyJackson@warriors.com |       2 |
|                6 | Paul      | 2022-06-02 | Paulryan@aol.com          |       5 |
|                7 | Jack      | 2022-05-02 | JacksonSmith@hotmail.com  |       1 |
+------------------+-----------+------------+---------------------------+---------+

I can write a left join, but i'm confused on how I can get the LAST Event (most recent event).

SELECT Name, Email, event.EventName, Date_order
FROM CustomerOrders
LEFT JOIN event
ON CustomerOrders.EventID = event.EventID;

+-----------+---------------------------+---------------------+------------+
| Name      | Email                     | EventName           | Date_order |
+-----------+---------------------------+---------------------+------------+
| Paul      | Paulryan@aol.com          | Rolling Stones      | 2022-02-02 |
| Max       | Maxwellsmith@aol.com      | Ike and Tina Turner | 2022-03-02 |
| Jack      | JacksonSmith@hotmail.com  | Yankees Vs Red Sox  | 2022-02-02 |
| Alexander | ALexDex@gmail.com         | Disney on Ice       | 2022-05-02 |
| Bobby     | BobbyJackson@warriors.com | Ike and Tina Turner | 2022-06-02 |
| Paul      | Paulryan@aol.com          | Yankees Vs Red Sox  | 2022-06-02 |
| Jack      | JacksonSmith@hotmail.com  | Rolling Stones      | 2022-05-02 |
+-----------+---------------------------+---------------------+------------+

I think I need to use distinct or unique keyword.

I need the output to be something like this:

+-----------+---------------------------+---------------------+------------+
| Name      | Email                     | EventName           | Date_order |
+-----------+---------------------------+---------------------+------------+
| Max       | Maxwellsmith@aol.com      | Ike and Tina Turner | 2022-03-02 |
| Alexander | ALexDex@gmail.com         | Disney on Ice       | 2022-05-02 |
| Bobby     | BobbyJackson@warriors.com | Ike and Tina Turner | 2022-06-02 |
| Paul      | Paulryan@aol.com          | Yankees Vs Red Sox  | 2022-06-02 |
| Jack      | JacksonSmith@hotmail.com  | Rolling Stones      | 2022-05-02 |
+-----------+---------------------------+---------------------+------------+

Which dbms are you using? (Different products have different options.) — jarlh, Mar 03 '22 at 19:14
Does this answer your question? [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — philipxy, Mar 03 '22 at 23:33

Ergest Basha · Answer 1 · 2022-03-03T19:53:55.810

If you identify unique customer by name, on most dbms this should work:

  with cte as ( 
           select idCustomerOrders,
                  Name,Email,
                  Date_order,
                  EventID,
                  row_number() over(partition by Name order by Date_order desc ) rownum
from CustomerOrders
            )
select Name,Email,EventName,Date_order
from cte 
left join Event on cte.EventID=Event.EventID
where cte.rownum=1;

Demo

If you have unique key on (Name,Email), you might use:

 with recursive cte as (
                       select  Name,
                               Email,
                               Date_order,
                               EventID,
                               concat(Name,'-',Email) as unique_customer
from CustomerOrders ),
cte2 AS ( select   Name,
                   Email,
                   Date_order,
                   EventID, row_number() over(partition by unique_customer order by Date_order desc) rownum
 from cte  )
select Name,Email,EventName,Date_order
from cte2 
left join Event on cte2.EventID=Event.EventID
where cte2.rownum=1;

Demo

That's great unless you add a purchase by Bobby with e-mail address bobbydarin@crooners.net to the list of people buying things. Is Name the unique identifier for a customer? — StoneGiant, Mar 03 '22 at 19:38
`Is Name the unique identifier for a customer?` , I don't know, only `OP` knows, I assumed that the customer are identified by the name based on the data example even though I agree with you, maybe better should be a unique key (Name,Email) — Ergest Basha, Mar 03 '22 at 19:43

SQL Left Join on Tables

1 Answers1