Geek Answers Handbook

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

I have many, many relationships in MySQL table with three tables: tickets, ticket_solutionsand solutions. (A ticket can have multiple decisions, and decisions 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 of the fields INTother than solutions.solutionthat VARCHAR.) Because some tickets are not finalized, they can be no solutions.

I wrote 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;

LEFT JOIN. , (ticket_solutions) , , solutions. , INNER JOIN , , .

, NULL tickets . (Ticket - .)

LEFT JOIN solutions, NULL?

, ?

+5
3

:

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;
+7

SQL ticket_solutions, , .

,

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

join_solutions , - , .

+1

. ,

IFNULL(GROUP_CONCAT(DISTINCT sol.solution SEPARATOR ', '), '')

.

, , : http://www.sqlfiddle.com/#!2/54c6f/3/0

0

More articles:


All Articles