LEFT OUTER JOIN Problem 1

[ElJayWilson]

I have a table that has some records that are the same other than one of the fields is NULL in one record and populatated in another record (same AwardNumber, different ActivityNumbers). The following view will not pull records due to there being instances of NULL values for Mnemonic in the CHAOS_Awards table. For example:

AwardNumber ActivityNumber Mnemonic
1000 1 NULL
1000 2 ITMER

The query used currently will not pull records because of that one record for AwardNumber 1000 that has a NULL value in Mnemonic.

I am having a hard time modifying the query so that I will get records unless there are no records for the JOINed AwardNumber that are NOT NULL.

Here is the current SQL:

SELECT     TOP (100) PERCENT ca.Mnemonic, sv.award_fk, pp.visit_fk, pp.AccountNumber, SUM(pp.quantity * p.UnitPrice) AS totalVisitCost, 
                      pc.amount AS pendingChangeTotal, sv.subject_fk, ISNULL(st.FirstName, '') + ' ' + ISNULL(st.MiddleInitial, '') + ' ' + ISNULL(st.LastName, '') 
                      AS participantName, sv.is_reconciled
FROM         dbo.Procedures_Performed AS pp INNER JOIN
                      dbo.ProceduresPermitted_tbl AS p ON p.ProcedureID = pp.procedure_fk INNER JOIN
                      dbo.Scheduled_Visits AS sv ON sv.id = pp.visit_fk INNER JOIN
                      dbo.Subjects_tbl AS st ON sv.subject_fk = st.SubjectID LEFT OUTER JOIN
                      CHAOS.dbo.CHAOS_Awards AS ca ON ca.AwardNumber = sv.award_fk AND ca.Mnemonic IS NOT NULL LEFT OUTER JOIN
                      dbo.Reconciliation_Pending_Changes AS pc ON pc.visit_fk = sv.id AND pc.completeDate IS NULL
WHERE     (sv.award_fk <> 1865) AND (ca.[Finalized Mnemonic] = '0')
GROUP BY ca.Mnemonic, sv.award_fk, pp.visit_fk, pp.AccountNumber, pc.amount, sv.subject_fk, ISNULL(st.FirstName, '') + ' ' + ISNULL(st.MiddleInitial, '') 
                      + ' ' + ISNULL(st.LastName, ''), sv.is_reconciled

Please help...

Thanks!

 

[bborissov]

.....
LEFT JOIN CHAOS.dbo.CHAOS_Awards ca 
     ON ca.AwardNumber = sv.award_fk AND
        ca.Mnemonic IS NOT NULL      AND
        ca.[Finalized Mnemonic] = '0'
...
WHERE (sv.award_fk <> 1865) 
GROUP BY ca.Mnemonic,
         sv.award_fk,
         pp.visit_fk,
         pp.AccountNumber,
         pc.amount,
         sv.subject_fk,
         ISNULL(st.FirstName, '')    + ' ' +
              ISNULL(st.MiddleInitial, '')+ ' ' +
              ISNULL(st.LastName, ''),
         sv.is_reconciled

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

There was a post by George Mastros recently where he explains WHY you should NOT put reference to LEFT/RIGHT JOINed table in WHERE clause. Very good explanation.
In short if you put such reference in WHERE clause (as you did) that reference automatically changes LEFT/RIGHT join to INNER one, just because NULLs are not equal; or different that anything, even NULLs.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ElJayWilson]

That worked, but I am getting a value of NULL for the Mnemonic field. I would be getting a real value (something like "ITMER")

Is is possible to select the matching record in CHAOS_Awards that doesn't have the Mnemonic of "NULL"?

BTW - Thanks for the tips on the where statement. I need all the help I can get .

 

[bborissov]

Not sure what you want. if you have NO matching records in CHAOS_Awards table you always will get NULL as Mnemonic. If you have at least one record where Mnemonic field is NOT NULL in CHAOS_Awards you'll get it. If you have more tahn one record you'll get as many records in your final record set as the records in CHAOS_Awards.
Par example:

[COLOR=green]-- That simulates your Procedures_Performed table 
[/color][COLOR=blue]DECLARE[/color] @PP [COLOR=blue]Table[/color] (AwardNumber [COLOR=blue]int[/color])

[COLOR=green]-- That simulates your Procedures_Performed table 
[/color][COLOR=blue]DECLARE[/color] @ca [COLOR=blue]Table[/color] (AwardNumber [COLOR=blue]int[/color], Mnemonic [COLOR=blue]varchar[/color](20) NULL)

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @pp [COLOR=blue]VALUES[/color] (1)

[COLOR=green]--- Check the resultset to see what happens when CA have
[/color][COLOR=green]--- NO matching records for some AwardNumber
[/color][COLOR=blue]SELECT[/color] [COLOR=red]'NO matching records for some AwardNumber'[/color] [COLOR=blue]AS[/color] Description, * [COLOR=blue]FROM[/color] @Pp PP
      [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] @Ca Ca [COLOR=blue]ON[/color] Pp.AwardNumber = Ca.AwardNumber AND
                          Ca.Mnemonic [COLOR=blue]IS[/color] NOT NULL



[COLOR=green]--- Check the resultset to see what happens when CA have
[/color][COLOR=green]--- one matching record, but it is NULL
[/color][COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @ca [COLOR=blue]VALUES[/color] (1, NULL)

[COLOR=blue]SELECT[/color] [COLOR=red]'one matching record, but it is NULL'[/color]  [COLOR=blue]AS[/color] Description, * [COLOR=blue]FROM[/color] @Pp PP
      [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] @Ca Ca [COLOR=blue]ON[/color] Pp.AwardNumber = Ca.AwardNumber AND
                          Ca.Mnemonic [COLOR=blue]IS[/color] NOT NULL


[COLOR=green]--- Check the resultset to see what happens when CA have
[/color][COLOR=green]--- two matching records, but one of them is NULL
[/color][COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @ca [COLOR=blue]VALUES[/color] (1, [COLOR=red]'Not NULL'[/color])

[COLOR=blue]SELECT[/color] [COLOR=red]'two matching records, but one of them is NULL'[/color] [COLOR=blue]AS[/color] Description,* [COLOR=blue]FROM[/color] @Pp PP
      [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] @Ca Ca [COLOR=blue]ON[/color] Pp.AwardNumber = Ca.AwardNumber AND
                          Ca.Mnemonic [COLOR=blue]IS[/color] NOT NULL


[COLOR=green]--- Check the resultset to see what happens when CA have
[/color][COLOR=green]--- more that two matching records
[/color][COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @ca [COLOR=blue]VALUES[/color] (1, [COLOR=red]'Not NULL 2'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @ca [COLOR=blue]VALUES[/color] (1, [COLOR=red]'Not NULL 3'[/color])

[COLOR=blue]SELECT[/color] [COLOR=red]'more that two matching records'[/color] [COLOR=blue]AS[/color] Description, * [COLOR=blue]FROM[/color] @Pp PP
      [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] @Ca Ca [COLOR=blue]ON[/color] Pp.AwardNumber = Ca.AwardNumber AND
                          Ca.Mnemonic [COLOR=blue]IS[/color] NOT NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ElJayWilson]

I understand what you are saying, but actually - I don't want any records returned when there are no matching records in CHAOS_Awards. I should get back nothing. What I am getting now is records with the Mnemonic field being NULL.

Here is an example of two records in the table for a given account:

AwardNumber     ActivityNumber     Mnemonic     Finalized Mnemonic
952	1	RiJacobs	NULL	False
952	2	joRomero	ESS	True

Here is the current query:

SELECT     ca.Mnemonic, sv.award_fk, pp.visit_fk, pp.AccountNumber, SUM(pp.quantity * p.UnitPrice) AS totalVisitCost, 
                      pc.amount AS pendingChangeTotal, sv.subject_fk, ISNULL(st.FirstName, '') + ' ' + ISNULL(st.MiddleInitial, '') + ' ' + ISNULL(st.LastName, '') 
                      AS participantName, sv.is_reconciled
FROM         dbo.Procedures_Performed AS pp INNER JOIN
                      dbo.ProceduresPermitted_tbl AS p ON p.ProcedureID = pp.procedure_fk INNER JOIN
                      dbo.Scheduled_Visits AS sv ON sv.id = pp.visit_fk INNER JOIN
                      dbo.Subjects_tbl AS st ON sv.subject_fk = st.SubjectID LEFT JOIN
                      CHAOS.dbo.CHAOS_Awards AS ca ON ca.AwardNumber = sv.award_fk AND ca.Mnemonic IS NOT NULL AND ca.[Finalized Mnemonic] = '0' LEFT OUTER JOIN
                      dbo.Reconciliation_Pending_Changes AS pc ON pc.visit_fk = sv.id AND pc.completeDate IS NULL
WHERE     (sv.award_fk <> 1865) AND  pp.AccountNumber = '9XXXXXXX'
GROUP BY ca.Mnemonic, sv.award_fk, pp.visit_fk, pp.AccountNumber, pc.amount, sv.subject_fk, ISNULL(st.FirstName, '') + ' ' + ISNULL(st.MiddleInitial, '') 
                      + ' ' + ISNULL(st.LastName, ''), sv.is_reconciled

This is what I get back:

Mnemonic	award_fk	visit_fk	AccountNumber	totalVisitCost	pendingChangeTotal	subject_fk	participantName	is_reconciled
NULL	952	1XXX	9XXXXXXX	96.75	NULL	2XXX	JANE DOE	1
NULL	952	1XXX	9XXXXXXX	102.25	NULL	2XXX	JANE DOE	1
NULL	952	1XXX	9XXXXXXX	90.00	NULL	2XXX	JANE DOE	1
NULL	952	1XXX	9XXXXXXX	90.00	NULL	2XXX	JANE DOE	1

What am I missing?

Thanks for all the help...

 

[bborissov]

If you do NOT want ANY records if there are NO matching records in CHAOS_Awards then change LEFT JOIN to INNER JOIN.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.