Multiple Outer Joins and Criteria

[LMGroup]

I'm using SQL Server 2005. I need all the records from OpenJob regardless of whether they appear in either CT_LMGeneral or CT_OTD. All records in OpenJob have corresponding ones in Customer and Salesperson. I'm having difficulty with the criteria and outer joins on CT_LMGeneral and CT_OTD. Here is what I have so far:

SELECT	OpenJob.JobN, 
		
CASE WHEN (CT_OTD.Component=1) THEN '*'
ELSE ' '
END as ' ',
		
OpenJob.JobN as 'Job', 
Customer.CustomerName as 'Cust', 
OpenJob.JobDescription as 'Description', 
Salesperson.Salesperson as 'Sales',		
CASE WHEN (CT_LMGeneral.LateDate <> '1/1/1900 12:00:00 AM') THEN convert(varchar(5),CT_LMGeneral.LateDate,101)+'*' 
ELSE convert(varchar(5),OpenJob.DueDate,101) 
END as 'Due', 
		
CASE WHEN (OpenJob.EarliestStartDate <> '1/1/1900 12:00:00 AM') THEN 
	CASE WHEN (CT_OTD.FilesNY = 1) THEN convert(varchar(5),OpenJob.EarliestStartDate,101) + ' ' + char(215)
	ELSE convert(varchar(5),OpenJob.EarliestStartDate,101)
	END
	ELSE ' '
END AS 'File',
				
CASE WHEN (CT_OTD.ProofOutP <> '1/1/1900 12:00:00 AM') THEN 
	CASE WHEN (CT_OTD.PDFOutNY = 1) THEN convert(varchar(5),CT_OTD.ProofOutP,101) + ' ' + char(215)
	ELSE convert(varchar(5),CT_OTD.ProofOutP,101)
	END
	ELSE ' '
END AS 'PDF Out',

CASE WHEN (CT_OTD.HProofOutP <> '1/1/1900 12:00:00 AM') THEN 
	CASE WHEN (CT_OTD.HCOutNY = 1) THEN convert(varchar(5),CT_OTD.HProofOutP,101) + ' ' + char(215)
	ELSE convert(varchar(5),HProofOutP,101)
	END
	ELSE ' '
END AS 'HC Out',

CASE WHEN (CT_OTD.ProofOKP <> '1/1/1900 12:00:00 AM') THEN 
	CASE WHEN (CT_OTD.PDFOKNY = 1) THEN convert(varchar(5),CT_OTD.ProofOKP,101) + ' ' + char(215)
	ELSE convert(varchar(5),ProofOKP,101)
	END
	ELSE ' '
END AS 'PDF OK',

CASE WHEN (CT_OTD.HProofOKP <> '1/1/1900 12:00:00 AM') THEN 
	CASE WHEN (CT_OTD.HCOKNY = 1) THEN convert(varchar(5),CT_OTD.HProofOKP,101) + ' ' + char(215)
	ELSE convert(varchar(5),HProofOKP,101)
	END
	ELSE ' '
END AS 'HC OK',

CASE WHEN (CT_OTD.MailDataInP <> '1/1/1900 12:00:00 AM') THEN 
	CASE WHEN (CT_OTD.MailDataNY = 1) THEN convert(varchar(5),CT_OTD.MailDataInP,101) + ' ' + char(215)
	ELSE convert(varchar(5),MailDataInP,101)
	END
	ELSE ' '
END AS 'Data',
	
CASE WHEN (CT_OTD.MailDropP <> '1/1/1900 12:00:00 AM') THEN 
	CASE WHEN (CT_OTD.MailDropNY = 1) THEN convert(varchar(5),CT_OTD.MailDropP,101) + ' ' + char(215)
	ELSE convert(varchar(5),MailDropP,101)
	END
	ELSE ' '
END AS 'Mail',
		
CASE WHEN (CT_OTD.CustCopyP <> '1/1/1900 12:00:00 AM') THEN 
	CASE WHEN (CT_OTD.AdvCopyNY = 1) THEN convert(varchar(5),CT_OTD.CustCopyP,101) + ' ' + char(215)
	ELSE convert(varchar(5),CustCopyP,101)
	END
	ELSE ' '
END AS 'Samples',
		
CASE WHEN (OpenJob.ShippedDate <> '1/1/1900 12:00:00 AM') THEN convert(varchar(5),OpenJob.ShippedDate,101)
ELSE ' '
END as 'Shipped',

CT_OTD.OnTime as 'OT'

FROM   Company.dbo.OpenJob
LEFT OUTER JOIN Company.dbo.CT_LMGeneral ON OpenJob.JobN=CT_LMGeneral.JobN
LEFT OUTER JOIN Company.dbo.CT_OTD ON OpenJob.JobN=CT_OTD.JobN [COLOR=#8AE234]--AND (CT_OTD.OnTime <> 'Yes' OR CT_OTD.OnTime <> 'No')[/color]
INNER JOIN Company.dbo.Customer ON OpenJob.CustomerN=Customer.CustomerN 
INNER JOIN Company.dbo.Salesperson ON OpenJob.SalesmanN=Salesperson.SalespersonN

WHERE [COLOR=#8AE234]--(CT_OTD.OnTime = ' ') AND[/color] 
((OpenJob.ShippedDate =  '1/1/1900 12:00:00 AM') OR (OpenJob.ShippedDate > '3/8/2013 12:00:00 AM')) 
AND (OpenJob.ProdPlanner=35)
AND OpenJob.JobStatus=0

ORDER BY OpenJob.JobN

I've been trying different variations on where to put the criteria for CT_OTD.OnTime (you can see it commented out in the FROM and WHERE statements). It doesn't seem to pick it up properly. I think that it's having trouble because, in many cases, there is no corresponding record in CT_OTD. In some cases, he statements run but return all the records instead of just the ones that OnTime is either blank or NULL. In other cases, I only get the records that exist in both tables.

Essentially, I need all the records from OpenJob where the ShippedDate is not 1/1/1900 or is greater than March 8 and the planner number equals 35 and the job is open (JobStatus=0) and I only want to see jobs that an OnTime status doesn't exist (OnTime is not equal to Yes or No) whether the field is blank or there is no record in the table at all. I know there must be a way, but I'm not sure where to enter that final criteria and have it process properly.

Thanks for any help!

 

[gmmastros]

When you use a left join, you should NOT put a filter in the where clause on it. Instead, you can put the condition in the ON clause.

Try this:

SELECT	ColumnName
FROM	Company.dbo.OpenJob
        LEFT OUTER JOIN Company.dbo.CT_LMGeneral 
          ON OpenJob.JobN=CT_LMGeneral.JobN
        LEFT OUTER JOIN Company.dbo.CT_OTD 
          ON OpenJob.JobN=CT_OTD.JobN
          [!]AND (NullIf(CT_OTD, '') Is NULL or CT_OTD.OnTime <> 'Yes' OR CT_OTD.OnTime <> 'No')[/!]
        INNER JOIN Company.dbo.Customer 
          ON OpenJob.CustomerN=Customer.CustomerN 
        INNER JOIN Company.dbo.Salesperson 
          ON OpenJob.SalesmanN=Salesperson.SalespersonN
WHERE ((OpenJob.ShippedDate =  '1/1/1900 12:00:00 AM') OR (OpenJob.ShippedDate > '3/8/2013 12:00:00 AM')) 
      AND (OpenJob.ProdPlanner=35)
      AND OpenJob.JobStatus=0
ORDER BY OpenJob.JobN

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LMGroup]

It's still giving me all the records instead of filtering out the ones where CT_OTD.OnTime is equal to Yes or No. Perhaps I just have to play around with that statement. Thanks for the help.

This is what the end of the code looks like now:

FROM   Company.dbo.OpenJob
     LEFT OUTER JOIN Company.dbo.CT_LMGeneral 
          ON OpenJob.JobN=CT_LMGeneral.JobN
     LEFT OUTER JOIN Company.dbo.CT_OTD 
          ON OpenJob.JobN=CT_OTD.JobN 
          AND (NullIf(CT_OTD.OnTime, '') Is NULL or (CT_OTD.OnTime <> 'Yes' OR CT_OTD.OnTime <> 'No'))
     INNER JOIN Company.dbo.Customer 
          ON OpenJob.CustomerN=Customer.CustomerN 
     INNER JOIN Company.dbo.Salesperson 
          ON OpenJob.SalesmanN=Salesperson.SalespersonN

WHERE   ((OpenJob.ShippedDate =  '1/1/1900 12:00:00 AM') OR (OpenJob.ShippedDate > '3/8/2013 12:00:00 AM')) 
        AND (OpenJob.ProdPlanner=35)
        AND OpenJob.JobStatus=0

ORDER BY OpenJob.JobN

 

[Olaf Doschke]

Every string is <>'Yes' Or <>'No', even Yes is not No and No is not Yes. So this part of your condition filters nothing, everything passes it. What do you really want?

You want to join records, only if OnTime is neither 'Yes', nor 'No'? Then you want OnTime to be Not 'YES' AND Not 'No'. Natural language vs. boolean algebra. Do you want tea or coffee? Yes!

Bye, Olaf.

 

[LMGroup]

The field has 4 possible values: No, Yes, blank, or NULL. I only want the last 2. Experimenting with it, when I put
AND CT_OTD.OnTime=''
it gives me the blank ones as well as all the other records but changes all the 'Yes' and 'No' values to NULL. If I change it from the blank to No or Yes, it changes the other to NULL and still brings those records up too.

I need all the records from OpenJob, except when there is a value in CT_OTD.OnTime that is either Yes or No. If it is blank or a record for the job doesn't exist in the CT_OTD table, I need the OpenJob values.

Perhaps I need to step away from it a bit and look at it with fresh eyes. I don't think I'm clearly explaining myself. I really appreciate the help you both have given me so far.