我需要有关以下sql Server查询的帮助,其中a.TAProfileID和c.CountryCode列在数据库中具有“NULL”值.
我希望我的JOIN语句返回存在的“NULL”值.
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.
所以,你的查询应该是这样的：
SELECT
a.ReservationStayID AS 'Reservation Id',a.PMSConfirmationNumber AS 'PMS No',c.NAME AS 'Travel Agency',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
