Question 280802 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How do I use just the first value that satisfies a condition in my join?

I’m trying to think what the most efficient way to do this is, hopefully you can help. I have two tables, one is for Customer Call data (customer calls in to a customer service number), the other is invoice data. They come from different sources so there is no shared PK-FK relationship. Here is some sample data:

CallData

CallId PhoneNumber Source CallDate
12345 555-555-5555 internet 2023-11-06
12346 555-666-6666 referral 2023-01-01
12347 555-666-6666 referral 2023-02-02
12348 555-666-6666 internet 2023-05-17
12349 555-777-7777 referral 2023-10-31
12350 555-777-7777 internet 2023-11-03

Invoice

InvoiceId PhoneNumber Amount InvoiceDate
11111 555-555-5555 250.56 2023-01-01
22222 555-666-6666 376.67 2023-02-01
33333 555-666-6666 761.09 2023-06-02
44444 555-666-6666 316.23 2023-07-03
55555 555-777-7777 145.33 2023-10-01
66666 555-777-7777 986.92 2023-11-01

I need to be able to join these two together based on the PhoneNumber. But I only care about records where the CallData.Source = ‘referral’ so that eliminates all but 3 CallData records from above. I then want to sum all the Invoice.Amount values but only if the InvoiceDate is greater than at least ONE of the CallDates.

So consider the CallData records with PhoneNumber 555-666-6666, there are 2 that are referral types and their CallDates are:

  • 2023-01-01
  • 2023-02-02

And in the Invoice table there are 3 invoices with InvoiceDates of:

  • 2023-02-01
  • 2023-06-02
  • 2023-07-03

All I care about is that all 3 InvoiceDates are AFTER at least one of the matching CallDates (in this case 2023-01-01), so I would sum all three and get 1453.99.

Please let me know if you have any further questions, thanks

Tags:

2

Answers


  1. 0

    You did not specify desired output, so:

    SELECT DISTINCT
   CallId,
   CallData.PhoneNumber,
   SUM(Amount) OVER (Partition BY CallId,CallData.PhoneNumber)  as "sum"
   
FROM CallData
INNER JOIN Invoice ON Invoice.PhoneNumber = CallData.PhoneNumber 
                  AND Invoice.InvoiceDate > CallData.CallDate
WHERE CallData.Source = 'referral'
  ;

    output:

    CallId PhoneNumber sum
    12346 555-666-6666 1454
    12347 555-666-6666 1077
    12349 555-777-7777 987

    see: DBFIDDLE

    90% of this SQL statement is a literal translation of what you wrote in your question into SQL syntax….

    or, just by PhoneNumber:

    SELECT DISTINCT

   CallData.PhoneNumber,
   SUM(Amount) OVER (Partition BY CallData.PhoneNumber)  as "sum"
   
FROM CallData
INNER JOIN Invoice ON Invoice.PhoneNumber = CallData.PhoneNumber 
                  AND Invoice.InvoiceDate > CallData.CallDate
WHERE CallData.Source = 'referral'
  ;

    see: DBFIDDLE

    Login or Signup to reply.
  2. 0

    You could use MIN() to get the minimum CallDate per PhoneNumber:

    SELECT Calls.*, SUM(Invoice.Amount) AS Amount
FROM (
    SELECT PhoneNumber, MIN(CallDate) AS MinCallDate
    FROM CallData
    WHERE Source = 'referral'
    GROUP BY PhoneNumber
) Calls
JOIN Invoice
    ON Invoice.PhoneNumber = Calls.PhoneNumber 
   AND Invoice.InvoiceDate >= Calls.MinCallDate
GROUP BY Calls.PhoneNumber;

    Output:

    PhoneNumber MinCallDate Amount
    555-666-6666 2023-01-01 1453.99
    555-777-7777 2023-10-31 986.92

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search