Select from two tables and showing results even if conditions are not met

Question

I'm making simple ticket system for internal use, not for public. I have two tables - tickets and ticket_answers. In tickets I store the who started the ticket and when along with body, title .. etc.

In ticket_answers I store who is replayed and when on particular ticket. So now on home page I want to show ticket info like: Author of ticket, body, title, date of ticket + last replay author, last replay date...

This is the query so far which is working BUT the ticket is shown on the page only if there is replay. If I create ticket will not be visible because doesn't have answers. So how to modify the query to show tickets with no replays?

SELECT tickets.*, ticket_answers.*
    FROM tickets, ticket_answers
    WHERE tickets.ticket_id = ticket_answers.ticket_id

    AND ticket_answers.ticket_answer_id
        IN (
              SELECT MAX( ticket_answer_id ) 
              FROM ticket_answers
              GROUP BY ticket_id
           )

I know why this is showing only tickets with answers because of WHERE clause but can't figure it out how to change the condition..

use left join: f.e. http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join — Mikey, Mar 15 '16 at 09:36

sagi · Accepted Answer · 2016-03-15T09:45:20.153

1

You need a left join, not an inner join.

SELECT tickets.*, ta.*
FROM tickets
LEFT OUTER JOIN ticket_answers
ON tickets.ticket_id = ticket_answers.ticket_id
 AND ticket_answers.ticket_answer_id  IN (SELECT MAX( ticket_answer_id ) 
                                          FROM ticket_answers
                                          GROUP BY ticket_id
                                           )

I think the IN statement is unecessary and you can avoid it by joining to a sub select that contain only the max ticket_answer for each ticket_id like this:

SELECT tickets.*, ticket_answers.*
FROM tickets
LEFT OUTER JOIN (select ticket_id,max(ticket_answer_id)
                 FROM ticket_answers
                 GROUP BY ticket_id) ta
ON tickets.ticket_id = ta.ticket_id

edited Mar 15 '16 at 09:45

answered Mar 15 '16 at 09:37

sagi

40,026
6
59
84

I every time messed up with joins. Still can't figure out which one to use and when. But eventually I will get it. First query works like charm. Thank's for the help! – S.I. Mar 15 '16 at 09:43
Just for information, second query return `Unknown table 'ticket_answers'`. – S.I. Mar 15 '16 at 09:44
@Garg Thats becuase you are using implicit join syntax(comma separated). Use the correct syntax and you will be able to understand it better – sagi Mar 15 '16 at 09:44
Yes, it's work in `SELECT` must be `tickets.*, ta.*` instead of `tickets.*, ticket_answers.*`. Anyway thank's again for help! – S.I. Mar 15 '16 at 09:47
Just a question what can be the reason to not showing me now `ticket_id` when I list results on the page? All other info is shown.. just `ticket_id` doesn't appear. – S.I. Mar 15 '16 at 09:56
Found the problem but don't know how to fix it. According to dev.mysql `MAX() returns NULL if there were no matching rows.` which means I can't take `ticket_id` until there is no answer to this ticket. – S.I. Mar 15 '16 at 10:27

Select from two tables and showing results even if conditions are not met

1 Answers1