conditional joins or a subquery 1

[jacck]

Hi all
I have a SQL view which joins a number of tables. One particular join is causing me trouble.

I am using a Left Outer Join between table C and D on the fields Lot_Id and Wh_Id.

I need the Table C record in all cases.
Records in table D may not exist (for any particular combination of Lot_Id and Wh_Id in Table C) but where they do, there might be 15 records in D and i only want the record which meets a certain criteria:
dbo.d.code = 'RM'

The problem is as soon as i put this criteria in the WHERE statement, no records are returned at all if there is no Table D record meeting this 'RM' criteria.

Have tried using Inner Join but same problem

I think i may need a subquery but do not know how to set this up

Any advice gratefully received

 

[Zathras]

The problem of course is that dbo.d.code is NULL when there is are no matching records in table D.

The solution is to use the IsNull( ) function to handle that case:

SELECT ... WHERE ... [b]AND IsNull(dbo.d.code,'RM')='RM'[/b] ...

 

[jacck]

Thanks Zathras

I just tried this now and it still results in no record being selected at all.

When i remove just this statement i get 15 records (none with CODE='RM') so i don't think there are any other errors in the statement

I'm not familiar with the syntax of isnull but should i be saying:
Not IsNull.....

I'll try that as well

 

[jacck]

tried

Not Isnull ....

and it returned the 15 records from Table D.

What i need is the one Table C record with a Null value in the dbo.d.CODE column

 

[r937]

your problem is that you have the condition in the WHERE clause

move it to the ON clause of the LEFT OUTER JOIN

select ...
  from tableC
left outer 
  join tableD 
    on tableD.Lot_Id = tableC.Lot_Id
   and tableD.Wh_Id  = tableC.Wh_Id
   [b]and[/b] tableD.code = 'RM'

r937.com | rudy.ca

 

[jacck]

EXCELLENT. Why didn't i think of that. Duh !

Thanks very much