Joining tables

[stivvyh]

Hi i am newbie here and to SQL and was hoping somebody could help me on this

I have two tables Bookings and Booking_History as below

BOOKINGS
------------------------------------------
| id | guest_name | location |
------------------------------------------
| 1 | person1 | spain |
| 2 | person2 | germany |
| 3 | person3 | italy |
-------------------------------------------

BOOKINGS_HISTORY
----------------------------------------
|id | book_id | action | total |
----------------------------------------
| 1 | 1 | confirmed | 200 |
| 2 | 1 | extra person | 300 |
| 3 | 2 | confirmed | 250 |
| 4 | 1 | minus person | 200 |
| 5 | 3 | confirmed | 300 |
| 6 | 3 | cancelled | 0 |
| 7 | 2 | extra bags | 300 |
----------------------------------------

The tables are more complicated but it gives the basic outline.

What i need to do is produce a table like below where it shows each
individual booking with the last action that was performed on it

-----------------------------------------------------
| id | guest_name | location | total |
------------------------------------------------------
| 1 | person1 | spain | 200 |
| 2 | person2 | germany | 0 |
| 3 | person3 | italy | 300 |
------------------------------------------------------

I know how to join the tables but do not no how to do a query based join.
Any help would be gratefully appreciated.

Steve

[almaz]

What action do you treat as "last"? With the biggest ID? If yes, try this solution:

select B.*, BH.* 
from
    BOOKINGS B 
inner join BOOKINGS_HISTORY BH 
    on B.id = BH.book_id
inner join 
    (
        select book_id, id = max(id) 
        from BOOKINGS_HISTORY
        group by book_id
    ) BH_LAST
    on BH.id = BH_LAST.id and BH.book_id = BH_LAST.book_id 
        and B.id = BH_LAST.book_id 

P.S. Note the last condition (B.id = BH_LAST.book_id). It duplicates condition on previous join, but allows SQL Server to choose the best join strategy.

[stivvyh]

Fantastic, after some tweeks here and there it worked a treat! I'm gonna spend some time looking at it now to make sure I totally understand it, I'm not gonna learn otherwise!

Many Thanks almaz