Question

I am at the task of joining 3 tables: Task, Unit, and Building.

The task table has a column for a unit and a column for a building.
Any single task is assigned to only a building OR a unit, never both. Thus one column in every record is always null. There are 6100 records in the task table.

当我使用这个join:

select * from task t
join building b on b.id = t.building_id;

我有628排,这是建筑工程的正确总数

当我用这支联军

select * from active_task at
inner join unit_template ut on ut.id = at.unit_template_id

I get 5472 rows. This is the correct number of unit tasks. If I add them up 5472+628 =6100 this is the correct # of rows in the task table.

当我运行这个查询时:

select * from task t
inner join unit ut on ut.id = t.unit_id
inner join building bt on bt.id = t.building_id

I get zero rows. I need my query to retrieve 6100 rows. Any help would be appreciated.

"https://i.sstatic.net/ivP6c.jpg" alt="基本ER"/>

Answer 1

尝试左侧加入 :

select * from task t
left join unit ut on ut.id = t.unit_id
left join building bt on bt.id = t.building_id

Answer 2

SELECT  *
FROM    task t
LEFT JOIN
        unit ut
ON      ut.id = t.unit_id
LEFT JOIN
        building bt
ON      bt.id = t.building_id
        AND t.unit_id IS NULL

Answer 3

如果您想要两个查询给出的所有匹配, 为什么不能统一 :

SELECT * from task t JOIN building b ON b.id = t.building_id
UNION
SELECT * from active_task at JOIN unit_template ut ON ut.id = at.unit_template_id

只要两个任务表格的字段数量相同,就足够了(否则就过滤选定语句中想要的列)。

友情链接