我有三张桌子:email,customer_new和现在,在电子邮件表中有一个名为类型的字段,该字段包含或1。
因此,如果customer_new类型包含,那么我希望从customer_old表中获取name列。
为此,我在下面进行了mysql查询:
SELECT email.*,
CASE email.`type`
WHEN 1 THEN customer_old.name
ELSE customer_new.name
END AS customer_name
LEFT JOIN
CASE email.`type`
WHEN 0 THEN customer_new ON email.customer_id = customer_new.id
ELSE customer_old ON email.customer_id = customer_old.id
END
FROM email现在,当我运行这个查询时,mysql总是抛出错误。
有人能根据条件通过案例陈述来解决我关于连接两个表的问题吗?
提前谢谢。
发布于 2015-05-30 07:44:07
尝试使用下面的查询。
SELECT email . * , IF( email.type = '1', customer_old.name, customer_new.name ) AS name
FROM email
LEFT JOIN customer_old ON ( customer_old.id = email.customer_id )
LEFT JOIN customer_new ON ( customer_new.id = email.customer_id )发布于 2015-05-30 07:52:46
select email.*,
CASE email.type
when 1 then customer_old.name
else customer_new.name
end as filteredname
from email,customer_new,customer_old
where
customer_new.id=email.id
and
customer_old.id=email.id;您可以使用左联接来优化执行。
发布于 2015-05-30 07:53:43
另一种选择,
SELECT * FROM email
LEFT JOIN customer_new AS customer ON email.customer_id = customer.id
WHERE email.type = 0
UNION
SELECT * FROM email
LEFT JOIN customer_old AS customer ON email.customer_id = customer.id
WHERE email.type = 1https://stackoverflow.com/questions/30543516
复制相似问题