Avatar of dyadmin
Flag for Canada

asked on 

Select Related Record in Table Join

Here is a simplified version of my problem. I am trying to create a view using two tables.

In the first table, "Parts" I have
 - PartID
 - Quantity

In the second table, "Audit", I have
 - PartID
 - Date
 - Reference Number

The two are joined using PartID and there are many audit references for each ID. I would like to select the latest date as well as the associated reference number. I can't seem to figure out the associated reference number part of it. Everytime I try to include it I get all the records from the audit table. I know this is a newbie question and I tried searching for an existing solution but any advice is greatly appreciated.

CREATE  VIEW  MostRecentTransaction as
		, B.Quantity
		, 'LastDate' = max (c.date)
		Parts B, Audit C
		B.PartID = C.PartID
	GROUP BY B.PartID, B.Quantity

Open in new window

Microsoft SQL ServerMySQL ServerPostgreSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon