有多种条件的加盟 [英] Join with multiple conditions

问题描述

我的整个查询有点复杂，因此我将尝试仅显示您需要了解的问题，我正在从数据库中获取票证，它显示标题，创建者，负责人票证和最后更新者，对于创建者和负责人，我在tickets_users.users_id中找到了他们的ID及其类型1或2，这取决于他们是票证的创建者还是负责人(标题)并将票证本身的所有信息表命名为tickets，现在为用户提供其ID，名称等，其名称为users

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

所以我的查询如下:

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 = ?");

我的问题在哪里JOIN users ON users.id = tickets_users.users_id对于负责人和创建者来说效果很好，但是如果不是负责人的人答复或修改票证，他将不会出现在最后修改者"中，因为last_updater 在表tickets中. 我试图用JOIN users ON (users.id = tickets_users.users_id AND , glpi_tickets.users_id_lastupdater)加入，但是没有用.有什么想法吗?

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?

推荐答案

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

还有

您需要将测试AND users.id = tickets_users.users_id添加到 您的CASE声明

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

工作了，谢谢 eggyal ！

这篇关于有多种条件的加盟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！