Join problem

[wbodger]

OK, I have a query that is giving me problems. First, here is the query

SELECT	DISTINCT
	Product			=pm.vchDescription,
	Serial_Number		=
	CASE 
		WHEN tm.vchSerialNumber LIKE '%invalid' THEN ''
		WHEN tm.vchSerialNumber = '' THEN cp.vchSerialNumber
		ELSE tm.vchSerialNumber -- Changed this to tMAsterSerialNumbers on 6/8/10
	END,
	Registration_Key	=tm.vchKey,  -- Added the Key data on 1/4/2007
	Registration_Date	=tm.dtAssigned,
	Path			=pme.vchfilePath,
	ProdNum		=pm.chProductNumber
	
FROM	Individual i
JOIN	CustomerProduct cp	ON i.iIndividualID = cp.iOwnerID
LEFT OUTER JOIN	tMasterSerialNumbers tm ON cp.iProductId = tm.iProductID -- changed this 6/8/10
JOIN	Product_Master pm	ON tm.vchProductNumber = pm.chProductNumber
JOIN 	ProductMaster_Extended pme ON pm.chProductNumber = PME.chProductNumber

WHERE	i.iIndividualID = xxxxxx
AND	cp.tiRecordStatus = 1

So, here is the issue. I have serial numbers in 2 tables. tMasterSerialNumbers is the repository for serial numbers, all unassigned (and assigned) serial numbers reside in this table. However, this table was implemented AFTER SNs were already in the other table, CustomerProduct (I know, I don't know how it happened, I wasn't here, just trying to clean it up). Also, we have times when one product will be fulfilled with several serial numbers assigned. When this happens, the record is placed into CustomerProduct with no serial number assigned, but there are the correct number of records in tMasterSerialNumbers with a matching iProductID for the record in CustomerProduct.

I had thought I could do CustomerProduct LEFT OUTER JOIN tMasterSerialNumbers on iProductid and get everything in CustomerProduct even if it isn't in tMasterSerialNumbers, but that isn't happening. If I search by iOwnerId in CustomerProduct, I find 5 records, two of which do not show up in tMasterSerialNumbers.

So, why is my left outer join not working the way I am expecting it to, is my thinking incorrect that it should grab everything from CustomerProduct even if there is no matching record in tMasterSerialNumbers?

Any help would be greatly appreciated and if I haven't provided enough info, let em know what else I should provide.

wb

 

[gmmastros]

My best guess...

You left join to the master table with the intent to get all rows from the left table (CustomerProduct). Normally, that's what a left join will do. But.... consider what the values will be when there is no match... The value will be NULL.

For example, if you left join to the tMasterSerialNumbers table, and there is no match, the vchProductNumber will be NULL. Then, you inner join to the Product_Master table on this column. Since join's don't match on NULLs, the absence of a row in in the Product_Master table will cause the row to NOT be returned.

Basically, what I am saying is... change the joins on Product_Master and ProductMaster_Extended to left joins. You should get the rows you expect after you do this.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

Ahh... I missed that, thanks!

wb