January 17, 2008 at 9:23 am
I have a query that joins information from 3 tables. when the data is presented I have null values in fields that i have joined. all data from sm2 is presented all others show a null value. any help would be most appreciated.
SELECT sm2.[GUESTNUM]
,sm3.[PIMGPATH]
,sm3.[PGNUM]
,sm2.[RESNO]
,sm2.[UNIT]
,sm2.[MOVABLE]
,sm2.[ACOMM]
,sm2.[DISCOUNT]
,sm2.[DISC]
,sm2.[GRAT]
,sm2.[EXTRAS]
,sm2.[OP]
,sm2.[NAME]
,sm2.[TYPE]
,sm2.[CRLIMIT]
,sm2.[SVCCHG]
,sm2.[GRPBAL]
,sm2.[MEMTYPE]
,sm2.[CMPTOTAL]
,sm2.[CMPBAL]
,sm2.[RCOST]
,sm2.[AUTOBILL]
,sm2.[VERSION]
,sm2.[INVSEG]
,sm2.[PKGBAL]
,sm2.[TSPOINTS]
,sm2.[LEDGERTYP]
,sm1.[sqlrecno]
,sm1.[LAST]
,sm1.[FIRST]
,sm1.[INITIAL]
,sm1.[TITLE]
,sm1.[ADDRESS1]
,sm1.[ADDRESS2]
,sm1.[ADDRESS3]
,sm1.[CITY]
,sm1.[STATE]
,sm1.[ZIP]
,sm1.[COUNTRY]
,sm1.[PHONE]
,sm1.[PHONE2]
,sm1.[LSTRATE]
,sm1.[LSTPACKAGE]
,sm1.[SOURCE]
,sm1.[REMARKS]
,sm1.[SEASON]
,sm1.[CREDIT]
,sm1.[GROUP]
,sm1.[VIP]
,sm1.[EXP]
,sm1.[MKT]
,sm1.[FAX]
,sm1.[CORP]
,sm1.[SECY]
,sm1.[OWNR]
,sm1.[AUTH]
,sm1.[GSTDIAL]
,sm1.[NATL]
,sm1.[PASSPRT]
,sm1.[GSTLNG]
,sm1.[PSSWD]
,sm1.
,sm1.[POSITION]
,sm1.[GREFERRAL]
,sm1.[ADDRESS4]
,sm1.[ADDRTYPE]
,sm1.[PROMO]
,sm1.[ALTNAME]
,sm1.[GSTLEVEL]
,sm1.[CNVAMT]
,sm1.[LAST2]
,sm1.[FIRST2]
,sm1.[PRIVLEV]
,sm1.[PRIVPROP]
,sm1.[sqlrecno]
FROM [HOSTSQL].[dbo].[IN_RES] sm2
left outer join IN_GUEST sm1 on sm1.GUESTNUM = sm2.GUESTNUM
left join IN_PLAYR sm3 on sm3.PIMGPATH = sm2.GUESTNUM
where sm2.MEMTYPE !=''
January 17, 2008 at 9:36 am
That is because you are using OUTER JOINS.
FROM [HOSTSQL].[dbo].[IN_RES] sm2
left outer join IN_GUEST sm1 on sm1.GUESTNUM = sm2.GUESTNUM
left join IN_PLAYR sm3 on sm3.PIMGPATH = sm2.GUESTNUM
where sm2.MEMTYPE !=''
When you use an outer join if there are rows in IN_RES that have no corrisponding row in IN_GUEST you will get the data for IN_RES and any columns from IN_GUEST will be filled in with NULLs. If this is not the results you want then you should use INNER JOINs or just JOIN.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
January 17, 2008 at 10:02 am
Thanks for the assistance. Greatly appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply