Returning Null when no records found using multiple tables and fields. 1

[NBVC]

I know I am messing up somehow, but not 100 % sure where....

I have this SQL statement, where I want to grab all records with criteria, but if a record doesn't exist in the JobOperations table I still want a "Null" record pulled indicating there are no operations corresponding to the JobAssemblies record. I tried using the CASE WHEN syntax, although I get no errors, I also don't get the "null" records I was hoping for.

Can someone guide me as to how to rearrange my SQL?

Note fields beginning in jmp belong to Jobs table, jma belong to JobAssemblies table and jmo belong to JobOperations table.


Select jmaJobID,jmaParentAssemblyID,jmaPartID,jmaPartShortDescription, jmoJobOperationID,(CASE WHEN jmoWorkCenterID is Null Then ' ' else jmoWorkCenterID END) as WC
FROM Jobs left outer join JobAssemblies on jmaJobID = jmpJobID Left Outer Join JobOperations On jmaJobID= jmoJobID And jmaJobAssemblyID = jmoJobAssemblyID
Where jmpClosed = 0 and jmpProductionComplete = 0 and jmaIssuedComplete = 0 and jmoProductionComplete = 0 and jmoWorkCenterID <> 'ASSY'

Thank you.

 

[NBVC]

I think I just figured it out

I just needed to add to my WHERE clause...

and jmoProductionComplete = 0 or jmoProductionComplete Is Null and jmoWorkCenterID <> 'ASSY' or jmoWorkCenterID Is Null

and I didn't have to use the CASE WHEN syntax at all. Istead I used COALESCE(jmoWorkCenterID, 'None Assigned') to return "None Assigned" when null record found.

This seems to work.

If anyone sees any possible flaws please advise.

Thanks.

 

[Olaf Doschke]

>and jmoProductionComplete = 0 and jmoWorkCenterID <> 'ASSY'
When you put conditions on a left outer joined table in the overal WHERE clause, you enforce these fields to NOT be NULL, therefore you make the left join an inner join.

Put such conditions into the join condition:

Select jmaJobID
     ,jmaParentAssemblyID
     ,jmaPartID
     ,jmaPartShortDescription
     ,jmoJobOperationID
     ,(CASE WHEN jmoWorkCenterID is Null Then ' ' else jmoWorkCenterID END) as WC
 FROM Jobs 
 LEFT OUTER JOIN JobAssemblies on jmaJobID = jmpJobID 
     [highlight #FCE94F]AND jmaIssuedComplete = 0[/highlight]
 LEFT OUTER JOIN JobOperations On jmoJobID = jmaJobID And jmoJobAssemblyID = jmaJobAssemblyID 
     [highlight #FCE94F]AND jmoProductionComplete = 0 and jmoWorkCenterID <> 'ASSY'[/highlight] 
 Where jmpClosed = 0 and jmpProductionComplete = 0

Relocated conditions are highlighted.

Besides, why is there a jobid (jmajobId) in the Joboperations? As it's two hierarchy levels beneath Jobs, you can have a mismatch of jobid by the relation over JobAsemblies and the direct relation to Jobs. Where you should normally have a cyclic reference, this can get a helirelation to another job.

If you do such double references, then at least add this column to the foreign key of JobOperations referencing JobAssemblies to get no such helix references to another job. That means the foreign key of JobOperations referencing JobAssemblies should be the columns (jmoJobID,jmoJobAssemblyID) referencing (jmaJobID,jmaJobAssemblyID) as in your join condition. That way you indirectly ensure jmoJobID=jmaJobID=jmpJobID. If you'd only let jmoJobAssemblyID reference jmaJobAssemblyID, you could reference a different jobid. This type of inheritance of a key over two (or more) hierarchy levels only is needed for specific other constraints or relations, maybe there are some here we don't see in this suibset of your database(s), so I don't say this is wrong, but it's suspicious.

Bye, Olaf.

 

[NBVC]

Thanks for your quick and informative reply, Olaf.

I applied your changes and it works

Thanks again.

 

[Olaf Doschke]

One more thing: With that specific CASE you also can use ISNULL() or COALESCE() and I'd opt for ISNULL, if the type of jmoWorkCenterID is char/varchar. The space then will be output as the type of the jmoWorkcenterID column.

In short: ...[tt]ISNULL(jmoWorkCenterID,' ') as WC
[/tt] Besides that, the simplest way of selecting [tt]jmoWorkCenterID as WC[/tt] would keep a NULL, but you could address that at the client level getting this NULL result value, you don't necessarily need a string here.

Bye, Olaf.

 

[NBVC]

Okay. Thanks again. I did try the Coalesce() and it seemed to work too. I haven't tried Isnull(). I will play with that also. I wanted to actually return a phrase like "None Assigned" when Null so I don't think I can use the "simplest way" in this case.

 

[Olaf Doschke]

Well, many frontend/client languages can convert NULL into whatever display value you want.

The thing with this column is, you normally want the jmoWorkCenterID in that field you alias as WC field. If jmoWorkCenterID is an integer you can't have an int and sometimes a string in WC, a query result still has to fulfill the rules of a table, a column is of a certain type in all records. You can only have a string in WC, if the jmoWorkCenterID column also is a string type (char, varchar, n(var)char...)

Bye, Olaf.