Combining between two tables in MySQL and getting the distinct answer-CodePudding

I know this has probably been asked before but I am trying to find the correct way to this and I have been struggling for the past week or so.

So we have two sets of data for example, one table is called 'Order Log' and another is called 'Customer Information'

and here are example of the two data set

NOTE: The order log will sometimes have order from the same customer twice

Order Log Table

Customer ID	Date	Order Number	Order Amount
sgcwi	2022-06-11	124	3
gbtfc	2022-07-09	12	4
crownwood	2022-04-08	123	1
kcsi	2022-02-24	543	1
ulsteri	2022-08-08	423	2
gbtfc	2022-07-08	1254	3
ulsteri	2022-04-08	345	2
kcsi	2022-07-13	64	1
crownwood	2022-07-04	55	1

Customer Information Table

Customer Name	Customer ID	Contact
Sagen Private	sgcwi	email
Granten Viel	gbtfc	phone
Crownwood LTD	crownwood	email
Kings College	kcsi	email
Ulster FC	ulsteri	phone

So what my question is, how do i write an sql query that gives me back the the last order for each customer from the Order Log table withhin a span of the last 6 Months and returns me back the customer name for each of those selected data from the customer Informationt table. like such

The Sql Query Return that i want

Customer Name	Customer ID	Date
Sagen Private	sgcwi	2022-06-11
Granten Viel	gbtfc	2022-06-11
Crownwood LTD	crownwood	2022-07-04
Kings College	kcsi	2022-07-13
Ulster FC	ulsteri	2022-08-08

so far I have figured out to get the result from the Log table that I gave to use the query

"SELECT DISTINCT orderLog.customerID FROM Order WHERE qslogv2.date >= DATE_ADD(NOW(), INTERVAL -3 MONTH);

But I am yet to figure out how do i connect the Customer Information table to this query so it returns me the appropriate customer name along with the query.

I tried using the above query that I mentioned and also tried the UNION keyword in MySQL but to my demise I was not able to get to a point where I got that desired result.

CodePudding user response：

Use JOIN-statement combined with MAX GROUP BY.

In JOIN you tell what columns match in the joined tables. In your case it is the Customer ID.

With GROUP BY, you divide the rows into sets (based on the customer) and then applies the MAX-function for each of those sets, so that you will get the latest date for each customer.

select 
    c.name,
    c.id,
    max(ol.date)
from customerInformation c
    join orderLog ol on ol.customerID=c.id
where ol.date between date_sub(now(), interval 6 month) and now()
group by c.name, c.id

CodePudding user response：

Using row_number you can select the latest order for each customer , then join with the CustomerInformation table.

with latest_order as (
     select *, row_number() over(partition by CustomerID order by `date` desc ) as lts_order
     from OrderLog
) select ci.CustomerName,lo.CustomerID,lo.`date`
  from latest_order lo 
  inner join CustomerInformation ci on ci.CustomerID=lo.CustomerID
  where lo.lts_order=1
  and lo.`date` between   DATE_SUB(now(), INTERVAL 6 MONTH) and now() ;

https://dbfiddle.uk/IC7aZzJi

lo.date between DATE_SUB(now(), INTERVAL 6 MONTH) and now() ; will select all rows before 6 months till now