August 31, 2009 at 10:12 am
Looking for help with a join question? I have 208 records in my FiveStar table and ~45K records in my SAP_Cust table. I wrote the following SQL:
SELECT DISTINCT
dbo_SAP_CUST.KUNNR,
dbo_SAP_CUST.ZCode,
FiveStar.Address,
dbo_SAP_CUST.STRAS,
dbo_SAP_CUST.NewAddy,
FiveStar.Name,
dbo_SAP_CUST.NAME1,
dbo_SAP_CUST.CGN,
dbo_SAP_CUST.VIP
FROM FiveStar
LEFT JOIN
dbo_SAP_CUST ON FiveStar.NewAddy=dbo_SAP_CUST.NewAddy
AND FiveStar.City=dbo_SAP_CUST.City
AND FiveStar.State=dbo_SAP_CUST.State
AND LEFT(FiveStar.Zip,5)=LEFT(dbo_SAP_CUST.Zip,5)
WHERE dbo_SAP_CUST.KUNNR < '0000900000'
ORDER BY dbo_SAP_CUST.STRAS
Expecting to get back ALL 208 records from the FiveStar table whether or not there is a match, yet all I get back are the records where a match exists?
August 31, 2009 at 10:21 am
That's because you have the condition on the SAP_Cust in the where clause, so are excluding NULLs (which are the non matching)
Alter your where clause to
WHERE dbo_SAP_CUST.KUNNR < '0000900000'
or dbo_SAP_CUST.KUNNR is null
ORDER BY dbo_SAP_CUST.STRAS
remark: Is KUNNR a character datatyped column ? Keep in mind these evaluate like literals and not like numberd ! (watch out for mixed data)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 31, 2009 at 10:22 am
The records are likely passing through your join and then being filtered by your WHERE. If the records don't exist, they can't have KUNNR < '0000900000'.
Try moving the WHERE clause into the join as an additional criteria (Like so:)
SELECT DISTINCT
dbo_SAP_CUST.KUNNR,
dbo_SAP_CUST.ZCode,
FiveStar.Address,
dbo_SAP_CUST.STRAS,
dbo_SAP_CUST.NewAddy,
FiveStar.Name,
dbo_SAP_CUST.NAME1,
dbo_SAP_CUST.CGN,
dbo_SAP_CUST.VIP
FROM FiveStar
LEFT JOIN
dbo_SAP_CUST ON FiveStar.NewAddy=dbo_SAP_CUST.NewAddy
AND FiveStar.City=dbo_SAP_CUST.City
AND FiveStar.State=dbo_SAP_CUST.State
AND LEFT(FiveStar.Zip,5)=LEFT(dbo_SAP_CUST.Zip,5)
AND dbo_SAP_CUST.KUNNR < '0000900000'
ORDER BY dbo_SAP_CUST.STRAS
August 31, 2009 at 10:43 am
ALZDBA (8/31/2009)
That's because you have the condition on the SAP_Cust in the where clause, so are excluding NULLs (which are the non matching)Alter your where clause to
WHERE dbo_SAP_CUST.KUNNR < '0000900000'
or dbo_SAP_CUST.KUNNR is null
ORDER BY dbo_SAP_CUST.STRAS
Much thanks! Looks like this did the trick...
remark: Is KUNNR a character datatyped column ? Keep in mind these evaluate like literals and not like numberd ! (watch out for mixed data)
Can you direct me to some info to help research this? I know the field is a text string in by database (and should really be a number since it's the PK) but other than use '' I'm not sure how else to deal with this...
August 31, 2009 at 1:47 pm
Ron (8/31/2009)
ALZDBA (8/31/2009)
That's because you have the condition on the SAP_Cust in the where clause, so are excluding NULLs (which are the non matching)Alter your where clause to
WHERE dbo_SAP_CUST.KUNNR < '0000900000'
or dbo_SAP_CUST.KUNNR is null
ORDER BY dbo_SAP_CUST.STRAS
Much thanks! Looks like this did the trick...
remark: Is KUNNR a character datatyped column ? Keep in mind these evaluate like literals and not like numberd ! (watch out for mixed data)
Can you direct me to some info to help research this? I know the field is a text string in by database (and should really be a number since it's the PK) but other than use '' I'm not sure how else to deal with this...
considered your example having leading zeroes, you should be fine if you are 100 % sure this will always be the case.
Just have a look at this little example :
Select cast('100' as varchar(15)) as TheChar
union all
select cast('0100' as varchar(15))
union all
select cast('1' as varchar(15))
union all
select cast('9' as varchar(15))
union all
select cast('A100' as varchar(15))
order by TheChar
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply