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

问题描述

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

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

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

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

推荐答案

要返回或产生NULL值，您将必须使用LEFT JOIN s.

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屋！