SQL Server 查询:使用 JOIN 包含 NULL 值 [英] SQL Server Query: Using JOIN to include NULL values

问题描述

我需要有关以下 SQL Server 查询的帮助，其中列 a.TAProfileID 和 c.CountryCode 在数据库中具有NULL"值.

我希望我的 JOIN 语句在它们存在的地方返回NULL"值.

SELECTa.ReservationStayID AS 'Reservation Id',a.PMSConfirmationNumber 为 'PMS No',a.CreatedOn AS '创建日期',a.ArrivalDate AS '到达日期',a.DepartureDate AS '出发日期',a.TAProfileID AS 'TA Id',a.StatusCode AS '状态',b.PropertyCode AS '酒店',c.名称为旅行社"，c.CountryCode AS '市场代码',d.CountryName AS 'Mkt'FROM 预订Stay a内部 JOIN GuestStaySummary b ON a.ReservationStayID = b.ReservationStayID内部 JOIN TravelAgency c ON a.TAProfileID = c.TravelAgencyID内部 JOIN 市场 d ON c.CountryCode = d.CountryCode

解决方案

为了返回或产生 NULL 值，你必须使用 LEFT JOINs.>

因此，您的查询应该类似于:

SELECTa.ReservationStayID AS 'ReservationId',a.PMSConfirmationNumber AS 'PMS No',a.CreatedOn AS '创建日期',a.ArrivalDate AS '到达日期',a.DepartureDate AS '出发日期',a.TAProfileID AS 'TA Id',a.StatusCode AS '状态',b.PropertyCode AS '酒店',c.NAME AS '旅行社',c.CountryCode AS '市场代码',d.CountryName AS 'Mkt'FROM 预订Stay aINNER JOIN GuestStaySummary b ON a.ReservationStayID = b.ReservationStayID左加入 TravelAgency c ON a.TAProfileID = c.TravelAgencyIDLEFT JOIN Market d ON c.CountryCode = d.CountryCode

I need help with the following SQL Server query where the columns a.TAProfileID and c.CountryCode have "NULL" values in the database.

I want my JOIN statements to return "NULL" values where they exist.

SELECT 
a.ReservationStayID AS 'Reservation Id',
a.PMSConfirmationNumber as 'PMS No',
a.CreatedOn AS 'Date Created',
a.ArrivalDate AS 'Date of Arrival',
a.DepartureDate AS 'Date of Departure',
a.TAProfileID AS 'TA Id',
a.StatusCode AS 'Status',
b.PropertyCode AS 'Hotel',
c.Name AS 'Travel Agency',
c.CountryCode AS 'Market Code',
d.CountryName AS 'Mkt'

FROM ReservationStay a

inner JOIN GuestStaySummary b ON a.ReservationStayID = b.ReservationStayID
inner JOIN TravelAgency c ON a.TAProfileID = c.TravelAgencyID
inner JOIN Market d ON c.CountryCode = d.CountryCode

解决方案

In order to return or produce NULL values you will have to use LEFT JOINs.

So, your query should be something like:

SELECT 
     a.ReservationStayID AS 'Reservation Id'
    ,a.PMSConfirmationNumber AS 'PMS No'
    ,a.CreatedOn AS 'Date Created'
    ,a.ArrivalDate AS 'Date of Arrival'
    ,a.DepartureDate AS 'Date of Departure'
    ,a.TAProfileID AS 'TA Id'
    ,a.StatusCode AS 'Status'
    ,b.PropertyCode AS 'Hotel'
    ,c.NAME AS 'Travel Agency'
    ,c.CountryCode AS 'Market Code'
    ,d.CountryName AS 'Mkt'
FROM ReservationStay a
    INNER JOIN GuestStaySummary b ON a.ReservationStayID = b.ReservationStayID
    LEFT JOIN TravelAgency c ON a.TAProfileID = c.TravelAgencyID
    LEFT JOIN Market d ON c.CountryCode = d.CountryCode

这篇关于SQL Server 查询:使用 JOIN 包含 NULL 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！