Using parent row in subquery where clause

[FishGuy]

Hopefully you can see from the code what I want to achieve but am currently unsuccesful.

I want to join on to each booking the latest record from from the audit table for that booking, however because the audit table is so big I would like to limit the nested select to only search for the records corresponding to the booking on each join iteration.

Currently though the statement does not recognise b as a table alias.

SELECT * FROM
Bookings as b
INNER JOIN 		
		(SELECT MAX(record_id) as LatestAuditID, bookingnumber 
		FROM audit a 
		WHERE a.bookingnumber=b.booking_number 
		GROUP BY bookingnumber
		) AS aud 
		ON aud.bookingnumber = b.booking_number

[FunkyDexter]

I'm not 100% sue I've understood but I think you're after something like this:-

Select B.Booking_Number, Max(A.RecordID)
From Bookings B
Inner Join Audit A
   on A.BookingNumber=B.Booking_Number 
Group By B.Booking_Number

No sub query required.

You can select additional fields from the Bookings table as long as you include them in the group by. You've already grouped by Booking Number (which I assume is the unique identifier for a booking) so grouping by extra fields on the booking table will not affect the way teh rows collapse. The same is not true if you want extra fields form the Audit table.

[CVMichael]

This will join Bookings with the latest audit:

SELECT b.*, aud.*
FROM Bookings as b
	INNER JOIN (
		SELECT au.*
		FROM audit AS au
			INNER JOIN (
				SELECT MAX(record_id) as LatestAuditID, bookingnumber
				FROM audit a
				GROUP BY bookingnumber
			) AS l ON l.LatestAuditID = au.record_id
	) AS aud ON aud.bookingnumber = b.booking_number

[dee-u]

Another stab.

Select B.*
FROM Bookings B
INNER JOIN Audit A
   ON A.BookingNumber = B.Booking_Number 
WHERE A.record_id IN 
(SELECT TOP 1 C.record_id FROM Audit C WHERE C.BookingNumber = B.BookingNumber ORDER BY C.record_ID)

[leinad31]

Hopefully you can see from the code what I want to achieve but am currently unsuccesful.

I want to join on to each booking the latest record from from the audit table for that booking, however because the audit table is so big I would like to limit the nested select to only search for the records corresponding to the booking on each join iteration.

Currently though the statement does not recognise b as a table alias.

SELECT * FROM
Bookings as b
INNER JOIN 		
		(SELECT MAX(record_id) as LatestAuditID, bookingnumber 
		FROM audit a 
		WHERE a.bookingnumber=b.booking_number 
		GROUP BY bookingnumber
		) AS aud 
		ON aud.bookingnumber = b.booking_number

Since the audit table is big we have to avoid full table scans... what are the available indices on audit table and bookings table? How many rows are in audit table and in bookings table?