CODEWITHSUNDEEP
mysql
S.I.
S.I.

Reputation: 3375

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

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..

Upvotes: 2

Views: 50

Answers (1)

sagi
sagi

Reputation: 40491

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

Upvotes: 1

Related Questions