asked on Help with SQL joins
I've inherited a database where the creator seems to not like using NULLs. There are text fields that cannot be NULLs and instead are set to blank. I need to join on these fields. I need to join between two tables where there are records that have blank for a value. I do not want the records that are returned where they are blank. A normal JOIN will match on there unwanted blank data values. How can I do JOIN to get around this?
Microsoft SQL ServerMicrosoft SQL Server 2005
Last Comment
HLRosenberger
Use nullif() on the join
Just add an extra condition?
SELECT b.*
FROM burgerjoints b
JOIN recordstores r ON b.id = r.id AND ISNULL(b.id, '') <> ''
Use this
SELECT b.*
FROM burgerjoints b
JOIN recordstores r ON ISNULL(b.id, -999) = ISNULL(r.id,-999)
ASKER
I have never used nullif. How is it used on a JOIN?
Try this
SELECT b.*
FROM burgerjoints b
JOIN recordstores r ON ISNULL(b.id, -999) = ISNULL(r.id,-999)
ASKER
Is it as simple as this. This seems to work.
INNER JOIN dbo.DEPREC ON dbo.calibration_test.instr
INNER JOIN dbo.DEPREC ON dbo.calibration_test.instr
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Scott is correct, forgot about that, so I'm going to amend my first comment.
Since there are no NULLs in the column per stated requirements, no need to handle them.
Since there are no NULLs in the column per stated requirements, no need to handle them.
SELECT b.*
FROM burgerjoints b
JOIN recordstores r ON b.id = r.id AND b.id <> ''
ASKER
Thanks!!!