SQL Join 3 tables with null values

Question

I have a query that returns members, their last visit and their last payment. My problem is that it doesn't return members without a visit and/or a payment.

I previously didnt include the last visits and I then had a query with LEFT and RIGHT JOINs instead of INNER but when I added the visit table I received som help to include it but we didn't notice that we were missing the members with null values in visit or payment.

I've tried applying LEFT and RIGHT JOINs without any luck. I've also tried adding eg. "OR (pt.member_id IS NULL)" also without success.

SELECT
    mr.member_id, 
    mr.name, 
    mr.tag, 
    pt.semester, 
    pt.date, 
    vt.date, 
FROM 
    members mr
INNER JOIN 
    payment pt 
ON 
    pt.member_id = mr.member_id 
    INNER JOIN 
        ( SELECT 
            member_id, 
            MAX(payment_id) max_value 
        FROM 
            payment 
        GROUP BY    
            member_id ) pt2 
    ON 
        pt.member_id = pt2.member_id 
    AND 
        pt.payment_id = pt2.max_value   
INNER JOIN 
    visit vt 
ON 
    vt.member_id = mr.member_id 
    INNER JOIN  
        ( SELECT    
            member_id, 
            MAX(date) max_visit_value 
        FROM 
            visit 
        GROUP BY 
            member_id ) vt2 
    ON 
        vt.member_id = vt2.member_id 
    AND 
        vt.date = vt2.max_visit_value

I want to get a result where visit and/or payment can be null.

I hope I make sense and that someone can help me :)

MySQL 5.6

In fact, left joining is one way to handle your situation. What exactly did you try with left joins? — Tim Biegeleisen, Jul 11 '19 at 13:48
Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". This is likely your left join problem, but you don't ask re that even though it doesn't do what you expect. [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/q/4707673/3404097) — philipxy, Jul 11 '19 at 18:38
Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) — philipxy, Jul 11 '19 at 18:38

score 0 · Answer 1 · answered Jul 11 '19 at 13:52

The following version using left joins everywhere might give you the result you want:

SELECT
    mr.member_id,
    mr.name,
    mr.tag,
    pt.semester,
    pt.date,
    vt.date,
FROM members mr
LEFT JOIN payment pt
    ON pt.member_id = mr.member_id
LEFT JOIN
(
    SELECT member_id, MAX(payment_id) max_value
    FROM payment
    GROUP BY member_id
) pt2
    ON pt.member_id = pt2.member_id AND pt.payment_id = pt2.max_value
LEFT JOIN visit vt
    ON vt.member_id = mr.member_id
LEFT JOIN
(
    SELECT member_id, MAX(date) max_visit_value
    FROM visit
    GROUP BY member_id
) vt2
    ON vt.member_id = vt2.member_id AND vt.date = vt2.max_visit_value;

score 0 · Answer 2 · answered Jul 11 '19 at 13:56

Correlated subqueries might be the simplest solution:

SELECT mr.member_id, mr.name, mr.tag, 
       (SELECT pt.semester
        FROM payment pt
        WHERE pt.member_id = mr.member_id
        ORDER BY pt.date DESC
        LIMIT 1
       ) as last_payment_semester
       (SELECT pt.date
        FROM payment pt
        WHERE pt.member_id = mr.member_id
        ORDER BY pt.date DESC
        LIMIT 1
       ) as last_payment_date
       (SELECT MAX(vt.date,)
        FROM visit vt
        WHERE vt.member_id = mr.member_id
        ORDER BY vt.date DESC
        LIMIT 1
       ) as last_visit_date
FROM members mr;

For performance, you want indexes on payment(member_id, date desc, semester) and visit(member_id, date desc).

Admittedly, having to repeat essentially the same subquery twice for the date and the semester is somewhat inelegant.

In MySQL 8+, you can use window functions:

SELECT mr.member_id, mr.name, mr.tag, pt.semester, pt.date, vt.date
FROM members mr LEFT JOIN
     (SELECT pt.*,
             ROW_NUMBER() OVER (PARTITION BY pt.member_id ORDER BY pt.date DESC) as seqnum
      FROM payment pt
     ) pt
     ON pt.member_id = mr.member_id AND pt.seqnum = 1 LEFT JOIN
     (SELECT pt.*,
             ROW_NUMBER() OVER (PARTITION BY vt.member_id ORDER BY vt.date DESC) as seqnum
      FROM visit vt
     ) vt
     ON vt.member_id = mr.member_id AND vt.seqnum = 1

This works, but even after indexing the query takes 7s – AntwonP Jul 11 '19 at 15:09 — AntwonP, Jul 11 '19 at 15:09
@AntwonP . . . And how does that compare to other methods? – Gordon Linoff Jul 11 '19 at 17:43 — Gordon Linoff, Jul 11 '19 at 17:43

Thorsten Kettner · Answer 3 · 2019-07-12T09:37:54.660

The biggest problem here is that a member can have many payments and many visits. You only want to show the latest of each. That is easy for the visits, as you only want to show the (maximum) date. For payments, however, you also want to show the semester belonging to the maximum date. If the semester is ascending like the date, then it's easy again: Use MAX(semester). If it isn't, then you must retrieve the maximum date row instead.

As of MySQL 8:

SELECT
  mr.member_id, 
  mr.name, 
  mr.tag, 
  pt.semester, 
  pt.date, 
  vt.date 
FROM members mr
LEFT JOIN 
(
  SELECT
    member_id,
    semester,
    date,
    MAX(date) OVER (PARTITION BY member_id) AS last_date
  FROM payment
) pt ON pt.member_id = mr.member_id AND pt.dateb = pt.last_date
LEFT JOIN
(
  SELECT    
    member_id, 
    MAX(date) AS max_visit_value 
  FROM visit 
  GROUP BY member_id
) vt ON vt.member_id = mr.member_id 
ORDER BY mr.member_id;

In earlier versions:

SELECT
  mr.member_id, 
  mr.name, 
  mr.tag, 
  pt.semester, 
  pt.date, 
  vt.date 
FROM members mr
LEFT JOIN 
(
  SELECT
    member_id,
    semester,
    date
  FROM payment
  WHERE (member_id, date) IN 
  (
    SELECT member_id, MAX(date)
    FROM payment
    GROUP BY member_id
  )
) pt ON pt.member_id = mr.member_id
LEFT JOIN
(
  SELECT    
    member_id, 
    MAX(date) AS max_visit_value 
  FROM visit 
  GROUP BY member_id
) vt ON vt.member_id = mr.member_id 
ORDER BY mr.member_id;

Hi, and thanks for the fast reply. I'm using MySQL 5.6 When trying your query I get the following error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY member_id) AS last_date FROM payment WHERE ( member_id, d' at line 21 — AntwonP, Jul 11 '19 at 15:15
Oh, sorry about this. This was due to copy & paste. I removed the offending line (`MAX ... OVER`). Please try again. — Thorsten Kettner, Jul 11 '19 at 15:24
Thanks a lot! :) With a little tweak I got it to work. I really appreciate it — AntwonP, Jul 12 '19 at 09:23

score 0 · Answer 4 · answered Jul 11 '19 at 14:17

LEFT JOIN can help if records are more in master table that would surely give you null visits/payments as those ids would not be there in the other joined table. If that isnt the case too try to debug via run subqueries seperately check for are there any null values encountered for visits/payments when run seperately.

score 0 · Accepted Answer · answered Jul 12 '19 at 09:25

A little tweak on Thorsten Kettner's answer made it work:

Thanks everyone :)

SELECT
    mr.member_id,
    mr.name,
    mr.tag,
    pt.semester,
    pt.date,
    vt.date
FROM members mr

LEFT JOIN
(
    SELECT 
        member_id, 
        semester, 
        date        
    FROM payment
    WHERE ( member_id, date ) IN
    (
        SELECT 
            member_id, 
            MAX(date)
        FROM 
            payment
        GROUP BY 
            member_id
    )
 ) pt ON pt.member_id = mr.member_id

 LEFT JOIN
 (
    SELECT
        member_id,
        date,
        door
    FROM visit
    WHERE ( member_id, date ) IN
    (
        SELECT 
            member_id, 
            MAX(date)
        FROM 
            visit
        GROUP BY 
            member_id
    )
 ) vt ON vt.member_id = mr.member_id

SQL Join 3 tables with null values

5 Answers5