471,122 Members | 1,128 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,122 software developers and data experts.

Joining tables

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
Feb 28 '07 #1
2 968
almaz
168 Expert 100+
What action do you treat as "last"? With the biggest ID? If yes, try this solution:
Expand|Select|Wrap|Line Numbers
  1. select B.*, BH.* 
  2. from
  3.     BOOKINGS B 
  4. inner join BOOKINGS_HISTORY BH 
  5.     on B.id = BH.book_id
  6. inner join 
  7.     (
  8.         select book_id, id = max(id) 
  9.         from BOOKINGS_HISTORY
  10.         group by book_id
  11.     ) BH_LAST
  12.     on BH.id = BH_LAST.id and BH.book_id = BH_LAST.book_id 
  13.         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.
Mar 1 '07 #2
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
Mar 1 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Job Lot | last post: by
2 posts views Thread by James | last post: by
3 posts views Thread by james | last post: by
1 post views Thread by Brian | last post: by
10 posts views Thread by Captain Nemo | last post: by
4 posts views Thread by Rnt6872 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.