Join with multiple conditions

问题:

my entire query is a bit complex so I'll try to just show what you need to understand my problem, I'm fetching tickets from a data base, it displays the title, the creator, the people in charge of the ticket, and the last updater, for the creator and the people in charge I find their ID in tickets_users.users_id and their type 1 or 2 depending if they are the creator of the ticket or if they are in charge, for the title and all the informations of the ticket itself the table is named tickets now for the users their ID their name etc it's users

So my query looks like that:

SELECT
tickets.id,
    tickets.name,
    GROUP_CONCAT(
            CASE WHEN tickets_users.type = 1 THEN 
                        CONCAT(users.firstname, ' ', users.realname)
            END) AS creator,
    GROUP_CONCAT(
            CASE WHEN tickets_users.type = 1 THEN 
                        CONCAT(tickets_users.users_id)
            END) AS creator_id,
    GROUP_CONCAT(
            CASE WHEN tickets_users.type = 2 THEN 
                        CONCAT(users.firstname, ' ', users.realname)
            END) AS in_charge,
    GROUP_CONCAT(
            CASE WHEN tickets_users.type = 2 THEN 
                        CONCAT(tickets_users.users_id)
            END) AS in_charge_id,
    tickets.date,
    tickets.priority,
    tickets.date_mod,
    tickets.status,
    tickets.users_id_lastupdater,
MAX(CASE WHEN tickets.users_id_lastupdater = users.id
         THEN CONCAT(users.firstname, ' ', users.realname) 
    END) AS last_updater,
tickets.content
FROM
    tickets
    JOIN tickets_users ON tickets_users.tickets_id = tickets.id
    JOIN users ON users.id = tickets_users.users_id
    WHERE tickets.id = ?");

My problem is there JOIN users ON users.id = tickets_users.users_id it works well for people in charge and creator, but if someone that is not in charge reply or modify the ticket he will not appear in "last modified by" because the last_updater ID is in the table tickets. I tried to JOIN with JOIN users ON (users.id = tickets_users.users_id AND , glpi_tickets.users_id_lastupdater) but it didn't work. Any ideas?

回答1:

ON users.id IN (tickets_users.users_id, tickets.last_updater)

And

You'd need to add the test AND users.id = tickets_users.users_id to your CASE statements

Worked, thanks eggyal!