NULL Values

dr223 · August 18, 2015, 11:22am

Hi,

I have the following select statement;

SELECT dbo.Consultation.ConsultationID, dbo.Consultation.PatientID, dbo.LkupConsultationType.ConsultationTypeID, dbo.Consultation.StaffID, CONVERT(VARCHAR(10), dbo.Consultation.ConsultationDate, 103) AS ConsultationDate, dbo.Consultation.Duration FROM dbo.Consultation WITH (tablock) INNER JOIN dbo.LkupConsultationType ON dbo.Consultation.ConsultationType = dbo.LkupConsultationType.ConsultationType

Now, in dbo.LkupConsultationType I have the following;

ConsultationTypeID    ConsultationType
1                        NULL
2                        Headache
3                        Fever

dbo.Consultation

ConsultationID        PatientID     ConsultationType       StaffID          ConsultationDate         Duration
1                             12               NULL               23              12/12/2008                  NULL
2                             54               Headache           78              12/12/2008                 NULL
3                             14               Fever              15              12/12/2008                  NULL

THE RESULTS from the select statement is;

ConsultationID        PatientID            ConsultationTypeID          StaffID          ConsultationDate           Duration
2                          54                        2                  78              12/12/2008                    NULL
3                          14                        3                  15              12/12/2008                   NULL

The ConsultationID with NULL Type is not pulled up, Why? How can I change the select statement to get the results as;

ConsultationID        PatientID            ConsultationTypeID       StaffID        ConsultationDate         Duration
    1                    12                        1                  23            12/12/2008                 NULL
    2                    54                        2                  78            12/12/2008                 NULL
    3                    14                        3                  15            12/12/2008                 NULL

I have tried a LEFT OUTER JOIN but its putting a NULL in the ConsultationTypeID instead of 1.

Thank you so much

Kristen · August 18, 2015, 11:43am

You need to use LEFT OUTER JOIN where rows from LkupConsultationType.ConsultationType do not exist.

You then need to display something that is NOT from [LkupConsultationType] in the column in situations where no matching row is found.

You can use COALESCE() (or ISNULL() if you prefer) for that:

COALESCE(dbo.LkupConsultationType.ConsultationTypeID
        , dbo.Consultation.SOME_COLUMN)
    AS [ConsultationTypeID]

Note that the data types have to be the same, so if your SOME_COLUMN is numeric you would need to use

COALESCE(dbo.LkupConsultationType.ConsultationTypeID
        , CONVERT(varchar(20), dbo.Consultation.SOME_COLUMN))
    AS [ConsultationTypeID]

dr223 · August 18, 2015, 11:48am

Tried;

   COALESCE(dbo.LkupConsultationType.ConsultationTypeID,
   dbo.Consultation.ConsultationType)
   AS [ConsultationTypeID]

It is bringing forward a NULL ConsultationTypeID instead of 1

What am I doing wrong?
Thanks

khtan · August 18, 2015, 12:09pm

what is the column name on dbo.Consultation table ConsultationType or ConsultationTypeID ?

Kristen · August 18, 2015, 12:33pm

How can it bring "1"? As I understand it there is no matching row in [LkupConsultationType], is that correct?

if so all you can do is to display something from [Consultation table instead (i.e. using COALESCE or ISNULL)

If your requirement is: "if there is no matching record in [LkupConsultationType] display 1"?? then you can use:

COALESCE(dbo.LkupConsultationType.ConsultationTypeID
        , '1')
    AS [ConsultationTypeID]

i.e. you need to describe what your "rule" is for the situation where there is no matching row in [LkupConsultationType]

dr223 · August 18, 2015, 12:59pm

Hi,

Sorry the LkupConsultationType is as shown - my mistake

ConsultationTypeID    ConsultationType
1                        NULL
2                        Headache
3                        Fever

and dbo consultation;

ConsultationID        PatientID     ConsultationType       StaffID          ConsultationDate         Duration
1                             12               NULL               23              12/12/2008                  NULL
2                             54               Headache           78              12/12/2008                 NULL
3                             14               Fever              15              12/12/2008                  NUL

Please check #1 again..

So if,
dbo.Consultation.ConsultationType = NULL
dbo.LkUpConsultationType.ConsultationType = NULL

Then extract = ConsultationTypeID = 1

I am so sorry for the confusion in the column names

Thank you all

dr223 · August 18, 2015, 1:37pm

Any help please ... Thanks

hhrr · August 18, 2015, 1:43pm

Hi

The problem is u r comparing NULL = NULL which SQL Server does not understand
as NULL = Unknown value when u do isnull(col,1) = isnull(col2,1) then it understands
and works

Please see below

SELECT a.ConsultationID
	,a.PatientID
	,b.ConsultationTypeID
	,a.StaffID
	,CONVERT(VARCHAR(10), a.ConsultationDate, 103) AS ConsultationDate
	,a.Duration
FROM #Consultation a WITH (TABLOCK)
LEFT JOIN #LkupConsultationType b ON isnull(a.ConsultationType, 1) = isnull(b.ConsultationType, 1)

/*
DROP TABLE #LkupConsultationType

CREATE TABLE #LkupConsultationType (
ConsultationTypeID INT NULL
,ConsultationType VARCHAR(100) NULL
)

INSERT INTO #LkupConsultationType
SELECT 1
,NULL

INSERT INTO #LkupConsultationType
SELECT 2
,'Headache'

INSERT INTO #LkupConsultationType
SELECT 3
,'Fever'

DROP TABLE #Consultation

CREATE TABLE #Consultation (
ConsultationID INT NULL
,PatientID INT NULL
,ConsultationType VARCHAR(100) NULL
,StaffID INT NULL
,ConsultationDate DATETIME NULL
,Duration INT NULL
)

INSERT INTO #Consultation
SELECT 1
,12
,NULL
,23
,'12/12/2008'
,NULL

INSERT INTO #Consultation
SELECT 2
,54
,'Headache'
,78
,'12/12/2008'
,NULL

INSERT INTO #Consultation
SELECT 3
,14
,'Fever'
,15
,'12/12/2008'
,NULL

SELECT *
FROM #LkupConsultationType

SELECT *
FROM #Consultation
*/

dr223 · August 18, 2015, 1:48pm

Ok,

isnull(a.ConsultationType, 1)

Instead of forcing it to be 1, can I have it read instead from dbo.LkupConsultationType

I am avoiding to type in 1 just incase the ConsultationTypeID changes in the next build for NULL values

Thank you

hhrr · August 18, 2015, 1:57pm

Hi

I dont think that would work

You can try

isnull(a.ConsultationType, 'some value that never happens')
= isnull(b.ConsultationType, 'some value that never happens')

dr223 · August 18, 2015, 2:00pm

So, what you are suggesting I have only two options here really;

Hard code the value you wish to have when the two NULLs match in both table (it is 1 in this example)
Don't use ISNULL and u'll receive the NULL as the ConsultationTypeID

There is no other way, the value I need to be in '1' position can be read from the LkupConsultationType table.

Thanks

Kristen · August 18, 2015, 2:00pm

OK, now I get it

I would recommend that you avoid that situation.

However, provided that there is only ever ONE row in [LkUpConsultationType] where [ConsultationType] column is NULL then you could do:

INNER JOIN dbo.LkupConsultationType 
    ON dbo.Consultation.ConsultationType = dbo.LkupConsultationType.ConsultationType
    OR
    (
            dbo.Consultation.ConsultationType IS NULL
        AND dbo.LkupConsultationType.ConsultationType IS NULL
    )

but it makes for a lot of inefficiency.

It is best to have a foreign key / join on a column which never changes and always has a value. Best are narrow width columns - so for example an INT surrogate key using an IDENTITY. All the records contains values 1, 2, 3 ... rather than wider string values like "Headache" or "Fever". Perhaps that is what your ConsultationTypeID column does? If so then I would suggest that you should have [ConsultationTypeID] values in your [Consultation] table and not [ConsultationType] values

In particular if the user can change values in ConsultationType - change the spelling, or modified it for a "Better" description, then joining on the ID value (i..e which never changes) is much more robust.

dr223 · August 18, 2015, 2:08pm

Kristen -

I cant avoid the situation

I am working already specified requirement. Secondly, I have 350 Million records not only 1 record. This was only to get an idea of how this problem can be resolved.

So am doomed to find a better approach or you advise me to retain the output to be a NULL value in ConsultationTypeID.

Thank you so much - Much appreciated

JamesK · August 18, 2015, 2:22pm

From what I understood, the issue is in the join condition.

    dbo.Consultation WITH ( TABLOCK )
            INNER JOIN dbo.LkupConsultationType 
        ON dbo.Consultation.ConsultationType = dbo.LkupConsultationType.ConsultationType

The join is not going to pull up any rows if either dbo.Consultation.ConsultationType or dbo.LkupConsultationType.ConsultationType is null. The dbo.LkupConsultationType.ConsultationType seems to have only non-null values (1, 2, and 3). So you are fine there. For dbo.Consultation.ConsultationType, if you do have nulls, and you want them to be considered as 1, change the join condition to

dbo.Consultation WITH ( TABLOCK )
        INNER JOIN dbo.LkupConsultationType 
    ON COALESCE(dbo.Consultation.ConsultationType,1) = dbo.LkupConsultationType.ConsultationType

Note: I wrote this a while ago and went away and came back only now. I see that Kristen and others have posted more useful responses.

Kristen · August 18, 2015, 2:43pm

There is another, possible, problem with the situation you find yourself in.

In your [LkupConsultationType] table I presume that ConsultationTypeID is a Unique INT - perhaps automatically generated using IDENTITY?

If so that makes it a great key to JOIN on. Unfortunately in your [Consultation] table you have a column for [ConsultationType] rather than [ConsultationTypeID], and furthermore the [ConsultationType] can contain NULLs.

We have discussed solutions to JOIN'ing on [ConsultationType] above.

However, my question is:

Are the values in [LkupConsultationType].[ConsultationType] guaranteed to be unique / distinct?
Otherwise when you join:

INNER JOIN dbo.LkupConsultationType 
    ON dbo.Consultation.ConsultationType = dbo.LkupConsultationType.ConsultationType
    OR
    (
            dbo.Consultation.ConsultationType IS NULL
        AND dbo.LkupConsultationType.ConsultationType IS NULL
    )

you will get two, or more, rows from [LkupConsultationType] matching your Consultation.ConsultationType value. That will make a mess of your report (multiple rows in the resultset for each row in [Consultation])

You also have to be certain that a value in LkupConsultationType.ConsultationType cannot be changed (because the rows with that value in Consultation.ConsultationType will then become orphans. If the relationship is enforced with a Foreign Key Constraint then you are fine. However, if NOT then this is an accident waiting to happen

You can check if there is already a Foreign Key Constraint on the relationship using

EXECUTE sp_help 'Consultation'

and looking at the section in the output for Constraints