Using left outer join for many-to-many relationship where null is allowed

Question

I have a many-to-many table relationship in MySQL involving three tables: tickets, ticket_solutions, and solutions. (A ticket may have multiple solutions, and solutions apply to multiple tickets.)

Here are the table structures, simplified:

tickets    ticket_solutions    solutions
-----      -----               -----
id         ticket_id           id
           solution_id         solution

(In this example, all fields are INT except solutions.solution which is VARCHAR.) Since some tickets aren't completed, they may not have any solutions.

I've written the following query:

SELECT t.id, GROUP_CONCAT(DISTINCT sol.solution SEPARATOR ', ') solutions
FROM tickets t
LEFT JOIN ticket_solutions tsol ON (tsol.ticket_id = t.id)
LEFT JOIN solutions sol ON (tsol.solution_id = sol.id)
GROUP BY t.id DESC;

My question lies with the second LEFT JOIN. In any situation where an entry exists in the linker table (ticket_solutions) for a given ticket, there will always be a record to match in solutions. However if I try to use an INNER JOIN instead, I no longer get tickets which lack solutions.

To my thinking, the only time NULL values will occur is in the relationship between tickets and the linker table. (Ticket without any solutions yet.)

Must I use a LEFT JOIN between the linker table and solutions even though there won't be NULL values in that particular relationship?

If not, what is the recommended query structure?

Answer 1

Try it this way:

SELECT t.id, GROUP_CONCAT(DISTINCT sol.solution SEPARATOR ', ') solutions
    FROM tickets t
        LEFT JOIN ticket_solutions tsol 
            INNER JOIN solutions sol 
                ON (tsol.solution_id = sol.id)
            ON (tsol.ticket_id = t.id)
    GROUP BY t.id DESC;