472,986 Members | 2,874 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,986 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

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

|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.

Feb 28 '07 #1
2 1021
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

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

Similar topics

by: Job Lot | last post by:
Is there anyway of Joining two or more DataTable with similar structure? I have three DataTables with following structures Data, AmountB/F, Repayments, InterestCharged and AmountC/F i want...
by: James | last post by:
Can anyone please shed some light on the following... I have a framework that uses dynamically created tables, named using an incremental "attribute set ID", as follows: attrdata_1 attrdata_2...
by: james | last post by:
Hi, I would like to get all the records from 9 tables that have the same field value in one field (it is a unique field)that is shared by all the tables. Would this method of joining work: ...
by: Brian | last post by:
I need help joining info from two tables. Table1 and Table 2 have the same fields(Node,Card,Slot,Facility,Sub-Port,Channel,Group,Cic) Group is text, rest are number. Table 1 contains records for...
by: Coquette | last post by:
Hi all. I'm new here. I wanted to ask your opinion about *joining two tables* from *two different database* together. Is *that* possible? My project requires me to join identical tables from...
by: Captain Nemo | last post by:
Hi I'm working on an ASP project where the clients want to be able to effectively perform SELECT queries joining tables from two different databases (located on the same SQL-Server). Does...
by: Reader | last post by:
Hello all, I am joining two tables in a query and the output should be all those records where two fields in table1 match two corresponding fields in table2. I joined the tables using both...
by: nkechifesie | last post by:
I have created 9 tables in access via visdata and need to create another table joining all these tables. I have used the query in visdata to do this but it gives me an outrageous view. I have entered...
by: Rnt6872 | last post by:
Table A Table B BOL# B_BOL# Chargeback# Hi All, I have been struggling with this for...
by: Supermansteel | last post by:
I am joining these 2 tables together in Access 2003 and can't figure out the exact way of writing this script......Can anyone help? I have the following SQL: SELECT...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
by: SueHopson | last post by:
Hi All, I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...

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.