MySQL中的条件内部联接语句 [英] Conditional inner join statements in MySQL

问题描述

有没有一种方法可以根据另一个表中的字段值有条件地更改我内部连接的表?这是我到目前为止(但出错)的结果:

Is there a way I can conditionally change which table i'm inner joining on based on the value of a field in another table? Here's what I got so far (but it errors) out:

SELECT 
    j.jobID, j.jobNumber,
    CASE
        WHEN j.idType = 'dealership' THEN d.dealershipName
        WHEN j.idType = 'Group' THEN g.groupName
        WHEN j.idType = 'Agency' then a.agencyName
    END as dealershipName,
    CASE
        WHEN p.manualTimestamp != '0000-00-00 00:00:00' THEN UNIX_TIMESTAMP(p.manualTimestamp)
        WHEN p.manualTimestamp = '0000-00-00 00:00:00' THEN p.timestamp
    END as checkTS,
    CONCAT_WS(' ', ui.fName, ui.lName) as salesRep
FROM jobs j
LEFT JOIN dealerships d ON j.dealershipID = d.dealershipID
LEFT JOIN dealershipgroups g ON j.dealershipID = g.groupID
LEFT JOIN agencies a ON j.dealershipID = a.agencyID
INNER JOIN payments p ON j.jobID = p.jobID
IF j.idType = 'dealership' THEN 
    INNER JOIN smdealershipjoins smdj ON j.dealershipID = smdj.dealership
    INNER JOIN userinfo ui ON smdj.sm = ui.userID           
ELSEIF j.idType = 'Group' THEN 
    INNER JOIN smgroupjoins gj ON j.dealershipID = gj.groupID
    INNER JOIN userinfo ui ON gj.sm = ui.userID
ELSEIF j.idType = 'Agency' THEN 
    INNER JOIN smagencyjoins aj ON j.dealershipID = aj.agencyID
    INNER JOIN userinfo ui on aj.sm = ui.userID
END IF

因此，有一个表(职位)具有idType(经销商，组或代理商)和一个clientID(称为DealershipID).我需要执行的操作是联接某个表(基于idType)以确定哪个销售经理拥有"该客户帐户.如果idType ='dealership'，我需要加入smdealershipjoins，如果是'Group'，我需要加入smgroupjoins，如果是'agency'，我需要加入smagencyjoins，这样我就可以将该表加入到userinfo中以获得销售量.经理的名字.

So, there's a table (jobs) that has and idType (dealership, group, or agency) and a clientID (called dealershipID). What I need to do it join a certain table (based on idType) to determine which sales manager "owns" the client account. If idType = 'dealership' I need to join smdealershipjoins, if it's 'Group' i need to join smgroupjoins, and if it 'agency' I need to join smagencyjoins so that I can then join that table to userinfo in order to get the sales manager's name.

我也尝试过使用case语句进行内部连接，但这也产生了错误.

I've also tried to use case statements to do the inner joining, but that gave an error too.

推荐答案

向左/外部联接这三个选项中的每一个，您选择的条件项将解决其余的问题.

Left / outer join to each of the three options and the conditional in your select will take care of the rest.

也许是这样的:

...
LEFT JOIN smdealershipjoins smdj 
    ON j.dealershipID = smdj.dealership
LEFT JOIN smgroupjoins gj 
    ON j.dealershipID = gj.groupID
LEFT JOIN smagencyjoins aj 
    ON j.dealershipID = aj.agencyID
LEFT JOIN userinfo ui 
    ON ui.userID = CASE
        WHEN j.idType = 'dealership' THEN smdj.sm
        WHEN j.idType = 'Group' THEN gj.sm
        WHEN j.idType = 'Agency' then aj.sm
    END

这篇关于MySQL中的条件内部联接语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！