基于第一表列与第二表连接

Question

嗨，我想把我的registry_ug表和第二个基于registry_ug.type的表连接起来.我想避免不必要的加入。

这是我的密码

   SELECT  ug.id , tbl.id FROM registry_ug ug
      JOIN    CASE ug.type 
             WHEN 1   THEN  users 
             WHEN 2   THEN  group_software 
       END
       AS  tbl
       ON 
       ug.id = tbl.id 

     WHERE ug.id = $uid

对我来说似乎没问题，但我明白这个错误。

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE ug.type WHEN 1 THEN users WHEN 2 THEN g' at line 2

SELECT ug.id , tbl.id FROM registry_ug ug JOIN CASE ug.type WHEN 1 THEN users WHEN 2 THEN group_software END AS tbl ON ug.id = tbl.id WHERE ug.id = 55 

*我不想加入到所有可能的第二桌*

Answer 1

你不能这样做。您不能以这种方式使用CASE表达式。试一试：

SELECT  
  ug.id, 
  COALESCE(u.id, g.id)
FROM registry_ug AS ug
LEFT JOIN users          AS u ON ug.id = u.id AND ug.type = 1
LEFT JOIN group_software AS g ON ug.id = g.id AND ug.type = 2
WHERE ug.id = $uid

Answer 2

试试看，

SELECT  ug.id , 
        CASE WHEN ug.type = 1 
            THEN b.id 
            ELSE c.id
        END newID
FROM    registry_ug ug
        LEFT JOIN   users b
            ON ug.id = b.id 
        LEFT JOIN   group_software c
            ON ug.id = c.id 
WHERE   ug.id = $uid

无法测试，没有添加样本数据