mysql查询：如何使用statment连接多个表

I have mysql query below. but it does not work.

我在下面有mysql查询。但它不起作用。

SELECT  CASE
        WHEN isLevel = '1' THEN 'Mahasiswa'
        WHEN isLevel = '2' THEN 'Dosen'
        WHEN isLevel = '3' THEN 'Karyawan'
        ELSE 'Admin'
        END as level 
FROM mhs LEFT JOIN if(isLevel=1,'mahasiswa','dosen') ON username=iduser 
WHERE blabala.

if "isLevel=1" I want to join mhs with the mahasiswa table, if "isLevel=2" join mhs with the dosen table, ... How should I write the query?

如果“isLevel = 1”我想加入mhs和mahasiswa表,如果“isLevel = 2”加入mhs与dosen表,...我应该如何编写查询?

please help me. thanks.

请帮帮我。谢谢。

2 个解决方案

#1

You can't join conditionally. But use 2 left joins to get both tables

你不能有条件地加入。但是使用2个左连接来获取两个表

Select  CASE
    WHEN isLevel = '1' THEN 'Mahasiswa'
    WHEN isLevel = '2' THEN 'Dosen'
    WHEN isLevel = '3' THEN 'Karyawan'
ELSE 'Admin'
END as level 
from mhs 
left join mahasiswa m on m.username=iduser
left join dosen d on d.username=iduser

#2

You could use a union statement here:

你可以在这里使用union语句:

SELECT 'Mahasiswa' AS level
FROM mhs, mahasiswa

WHERE isLevel = 1
AND username = iduser

UNION ALL

SELECT CASE WHEN isLevel = 2 THEN 'Dosen' 
            WHEN isLevel = 3 THEN 'Karyawan' ELSE 'Admin' END AS level
FROM mhs, dosen

WHERE isLevel != 1
AND username = iduser;

Although this does seem a difficult way to do it. You're probably better off in the long term putting the translations of the isLevel column into another table, so you can reuse. Unless you really enjoy writing CASE statements a lot.

虽然这似乎很难做到。从长远来看,将isLevel列的翻译放入另一个表中可能会更好,因此您可以重复使用。除非你真的很喜欢写CASE语句。

#1