多表自连接 - 有没有一个好方法可以做到这一点

Question

我试图使用此查询从数据库中提取一些信息，

SELECT dbo.AfterHoursParameterSetup.Customer_id
, dbo.tblCustomer.Company
, dbo.tblDevices.device_id
, dbo.AfterHoursParameterSetup.DeviceMasterID
, dbo.tblDriverType.DriverType
, dbo.tblGroups.GroupName
, dbo.AfterHoursParameterSetup.ReportBy
, dbo.AfterHoursParameterSetup.TripTimeFilter
, dbo.AfterHoursParameterSetup.chkWeekEndTravel
, dbo.AfterHoursParameterSetup.WeekdayStart
, dbo.AfterHoursParameterSetup.WeekDayEnd
, dbo.AfterHoursParameterSetup.WeekEndFrom
, dbo.AfterHoursParameterSetup.WeekEndTo
, dbo.AfterHoursParameterSetup.ReportType
FROM dbo.AfterHoursParameterSetup
FULL JOIN dbo.tblCustomer
ON dbo.AfterHoursParameterSetup.Customer_id = dbo.tblCustomer.Customer_id
FULL JOIN dbo.tblDriverType
ON dbo.AfterHoursParameterSetup.DeviceMasterID = dbo.tblDriverType.DriverType_id
FULL JOIN dbo.tblGroups
ON dbo.AfterHoursParameterSetup.DeviceMasterID = dbo.tblGroups.Group_id
FULL JOIN dbo.tblDevices
ON dbo.AfterHoursParameterSetup.DeviceMasterID = dbo.tblDevices.device_id
ORDER BY dbo.AfterHoursParameterSetup.Customer_id ASC

有没有其他方法可以构建此查询，以便我可以消除 NULL 值？ 德博。AfterHoursParameterSetup.DeviceMasterID 将所有内容链接在一起，但该列使用来自三个不同表的值，因此在没有匹配值的情况下，它将为不存在匹配连接值的所有其他选定列返回 NULL。

Answer 1

如果我理解你的问题，你想要表格的交集。如果是这样，则用户内部联接。

SELECT dbo.AfterHoursParameterSetup.Customer_id
    , dbo.tblCustomer.Company
    , dbo.tblDevices.device_id
    , dbo.AfterHoursParameterSetup.DeviceMasterID
    , dbo.tblDriverType.DriverType
    , dbo.tblGroups.GroupName
    , dbo.AfterHoursParameterSetup.ReportBy
    , dbo.AfterHoursParameterSetup.TripTimeFilter
    , dbo.AfterHoursParameterSetup.chkWeekEndTravel
    , dbo.AfterHoursParameterSetup.WeekdayStart
    , dbo.AfterHoursParameterSetup.WeekDayEnd
    , dbo.AfterHoursParameterSetup.WeekEndFrom
    , dbo.AfterHoursParameterSetup.WeekEndTo
    , dbo.AfterHoursParameterSetup.ReportType
FROM dbo.AfterHoursParameterSetup 
    INNER JOIN dbo.tblCustomer ON dbo.AfterHoursParameterSetup.Customer_id = dbo.tblCustomer.Customer_id
    INNER JOIN dbo.tblDriverType ON dbo.AfterHoursParameterSetup.DeviceMasterID = dbo.tblDriverType.DriverType_id
    INNER JOIN dbo.tblGroups ON dbo.AfterHoursParameterSetup.DeviceMasterID = dbo.tblGroups.Group_id
    INNER JOIN dbo.tblDevices ON dbo.AfterHoursParameterSetup.DeviceMasterID = dbo.tblDevices.device_id
ORDER BY dbo.AfterHoursParameterSetup.Customer_id ASC