SQL Join 3 tables with null values

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

Answer

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