SQL将2个表联接到1个表 [英] SQL join 2 tables to 1 table

问题描述

我的任务是连接3个表: 任务，单位和建筑物.

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

任务表具有一列用于单位和一列用于建筑物.
任何一项任务仅分配给建筑物或单元，而不会两者都分配. 因此，每条记录中的一列始终为空.任务表中有6100条记录.

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时:

when I use this JOIN:

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

我得到628行.这是正确的构建任务总数.

I get 628 rows. This is the correct total of building tasks.

当我使用此JOIN

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

我得到5472行.这是单元任务的正确数量. 如果我将它们加起来5472 + 628 = 6100，这就是任务表中的正确行数.

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

我得到零行.我需要查询才能检索6100行. 任何帮助将不胜感激.

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

推荐答案

尝试左联接:

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

这篇关于SQL将2个表联接到1个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！