mysql - MySQL外部连接与数据透视表

CREATE TABLE link (
    entityid integer,
    fileid INT (11) NULL,
    folderid INT (11) NULL
);

CREATE TABLE file (
    fileid integer,
    name varchar
);

CREATE TABLE folder (
    folderid integer,
    name varchar
);

我想获取附加到实体的文件和文件夹列表。尝试过以下查询，但是如果文件夹或文件中都没有记录，它将给出空记录

select fi.name,fo.name
FROM link ll
LEFT OUTER JOIN file fi ON lk.FileId = fi.FileId
LEFT OUTER JOIN folder fo ON lk.FileId = fo.FileId
WHERE dl.entityid = 307

样本数据

link
entityid    fileid  folderid
22          1       null
22          2       null
22          3       null
22          4       null
22          null    33


file
fileid  name
1       file1
2       file2
3       file3
4       file4

folder
folderid    name
33         folder33
34         folder34
35         folder35
36         folder36

最佳答案

只需将entityid添加到select子句中：

SELECT ll.entityid, fi.name, fo.name
FROM link ll
LEFT OUTER JOIN file fi ON lk.FileId = fi.FileId
LEFT OUTER JOIN folder fo ON lk.FileId = fo.FileId
WHERE dl.entityid = 307;

关于mysql - MySQL外部连接与数据透视表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49839748/