Using parent row in subquery where clause

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.

Code:

---

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

---

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

Code:

---

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.

This will join Bookings with the latest audit:

Code:

---

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

---

Another stab.

Code:

---

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)

---

Quote:

---

Originally Posted by 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.

Code:

---

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?