Left Outer Join Criteria Help 1

[AngelB]

Hi All

I have found similar problems to mine on this forum, but nothing that quite matches. I am using a left outer join from a products table to a product_info table which contains the date the product was entered into the system. For whatever reason, not every product has this date stored against it, but I need to return all products regardless. The way the product_info table is structured is causing the problem. There is a usage_number and a header_record for each piece of data in the table. Several different 'fields' are stored in one date_field, based on the value of the usage_number and header_record. For example:

usage_number = 10, header_record = 100, date_field = date_created
usage_number = 10, header_record = 102, date_field = date_of_last_order

I want to return the date_created so my code looks like:

SELECT Products.PRODUCT_CODE, Product_Info.DATE_FIELD
FROM Products LEFT OUTER JOIN Product_Info
ON Products.PROD_REC = Product_Info.LINKED_REC
WHERE Product_Info.USAGE_NUMBER = 10
AND Product_Info.HEADER_RECORD = 100

I know that the WHERE clause prevents the outer join from working, but what should my syntax be? I can't figure out a way to get back what I want.

Thanks

Geraint

 

[hneal98]

See if this would work:

SELECT 
	Products.PRODUCT_CODE, 
	Product_Info.DATE_FIELD
FROM 
	Products 
	LEFT OUTER JOIN Product_Info  ON Products.PROD_REC = Product_Info.LINKED_REC
						AND Product_Info.USAGE_NUMBER = 10
						AND Product_Info.HEADER_RECORD = 100

 

[AngelB]

Thank you very much, both for the quick reply and the fact that it worked like a charm.

V. Grateful

Geraint

 

[hneal98]

No problem.