基于列值的条件连接 [英] Conditional JOINs based on column value

问题描述

我正在尝试根据事件类型有条件地将一个主事件表连接到另外三个表.select 语句工作正常，并返回我期望的结果集，但是当我添加 JOIN 语句时，我收到一条错误消息，指出未找到列别名:

I'm trying to conditionally join one master event table to three others depending on an event type. The select statement works fine, and returns the result set I'd expect, but when I add the JOIN statements, I get an error saying the column aliases were not found:

SELECT 
event.type as type,
IF(type = 'birthday', event.target_id, NULL) as birthday_id,
IF(type = 'graduation', event.target_id, NULL) as graduation_id,
IF(type = 'wedding', event.target_id, NULL) as wedding_id
FROM event
LEFT OUTER JOIN birthday ON birthday_id = birthday.id
LEFT OUTER JOIN graduation ON graduation_id = graduation.id
LEFT OUTER JOIN wedding ON wedding_id = wedding.id

得到这个错误:

'on 子句'中的未知列'birthday_id'

Unknown column 'birthday_id' in 'on clause'

<小时>

更新: 好吧，Sebas 刚刚表示您无法加入计算结果，在这种情况下，我的方法已关闭.那么做这样的事情的正确方法是什么?

---

UPDATE: Ok Sebas just indicated you can't join on calculation results, in which case my approach is off. So what is the correct approach for doing something like this?

推荐答案

SELECT 
    event.type as type,
    IF(type = 'birthday', birthday.id, NULL) as birthday_id,
    IF(type = 'graduation', graduation.id, NULL) as graduation_id,
    IF(type = 'wedding', wedding.id, NULL) as wedding_id
FROM 
    event
        LEFT OUTER JOIN birthday b  ON event.target_id = b.id
        LEFT OUTER JOIN graduation g    ON b.id IS NULL AND event.target_id = g.id
        LEFT OUTER JOIN wedding w   ON b.id IS NULL AND g.id IS NULL AND event.target_id = w.id

应该可以解决问题，给我反馈！rgds.

should do the trick, give me feedback! rgds.

参见 IS NULL 条件.没测试过，不知道mysql会不会接受！如果是，那么几乎只完成必要的连接...

edit: See the IS NULL conditions. I didn't test it, I wonder if mysql would accept it! If yes, then almost only the necessary joins would be done...

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