Solved

Help with SQL joins

Posted on 2016-09-29
9
53 Views
Last Modified: 2016-10-05
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?
0
Comment
Question by:HLRosenberger
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 41821903
Use nullif() on the join
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41821907
Just add an extra condition?
SELECT b.* 
FROM burgerjoints b
   JOIN recordstores r ON b.id = r.id AND ISNULL(b.id, '') <> '' 

Open in new window

0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41821919
Use this

SELECT b.* 
FROM burgerjoints b
   JOIN recordstores r ON ISNULL(b.id, -999) = ISNULL(r.id,-999)

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:HLRosenberger
ID: 41821922
I have never used nullif. How is it used on a JOIN?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41821925
Try this

SELECT b.* 
FROM burgerjoints b
   JOIN recordstores r ON ISNULL(b.id, -999) = ISNULL(r.id,-999)

Open in new window

0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41821937
Is it as simple as this.  This seems to work.

INNER JOIN dbo.DEPREC ON dbo.calibration_test.instrument = dbo.DEPREC.dep_ser and calibration_test.instrument <> ''
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41822124
Do not use any function in any JOIN unless it's absolutely unavoidable, because it makes the comparison "non-sargable".  And ISNULL and NULLIF are always avoidable.

INNER JOIN dbo.DEPREC ON
    dbo.calibration_test.instrument > '' AND
    dbo.calibration_test.instrument = dbo.DEPREC.dep_ser
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41822146
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.
SELECT b.* 
FROM burgerjoints b
   JOIN recordstores r ON b.id = r.id AND b.id <> ''

Open in new window

0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 41830415
Thanks!!!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question