October 14, 2009 at 2:11 pm
Hi,
I need help with sql query
I have bookings in one table and bookingstates in another table which contains states related to booking
Booking Table Schema BookingStates Table Schema sates Table Schema
BookingID(primary key), StateID (primary key) StateID(primary key)
bookingtypeID BookingID(primary key) StateName
.
.
I have established relationships between bookingid in booking table and bookingstates table,stateid in states table with stateid in bookingstates
I dont have much exposure sql,on how to carry insert and update on multiple tables like booking table,booking states, as i states i can get is more than one min ,upto 5 how can i carry insert in this instance and when booking is edited i might get same number of states or booking can be removed how can i update the original one in this instance
Thanks for help
October 14, 2009 at 2:16 pm
And your question is.....??????
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2009 at 2:22 pm
forum member (10/14/2009)
Hi,I need help with sql query
I have bookings in one table and bookingstates in another table which contains states related to booking
Booking Table Schema BookingStates Table Schema sates Table Schema
BookingID(primary key), StateID (primary key) StateID(primary key)
bookingtypeID BookingID(primary key) StateName
.
.
I have established relationships between bookingid in booking table and bookingstates table,stateid in states table with stateid in bookingstates
I dont have much exposure sql,on how to carry insert and update on multiple tables like booking table,booking states, as i states i can get is more than one min ,upto 5 how can i carry insert in this instance and when booking is edited i might get same number of states or booking can be removed how can i update the original one in this instance
Thanks for help
Assuming I understand what you're asking for, you simply need to insert into the Booking table and then insert into the BookingState table. 5 a minute is not very much, so you should be fine with simple insert statements. Nothing fancy needed.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 14, 2009 at 2:48 pm
If i understood correctly, i need to specify 5 state variables and carry insert on them,please calrify and please suggest on how to carry update on this,some times a booking for a a state can be removed when edited in this instance how can i carry update , i might need to carry delete on the removed one but not sure how get the original one and compare them,not sure i am in correct direction with this
Thanks
October 14, 2009 at 2:51 pm
Kudos Grant, your response almost made me understand that. Almost.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 14, 2009 at 2:59 pm
forum member (10/14/2009)
If i understood correctly, i need to specify 5 state variables and carry insert on them,please calrify and please suggest on how to carry update on this,some times a booking for a a state can be removed when edited in this instance how can i carry update , i might need to carry delete on the removed one but not sure how get the original one and compare them,not sure i am in correct direction with thisThanks
Whoa! I'm lost. I don't get what you're saying here at all.
5 state variables... meaning that the BookingState has five different types that it can be set to?
I'm lost after that. You want to update, but not update, but delete, but compare, but get the original... Yeah, I'm sorry, but I'm stumped here.
Taking a guess... If there will always be five BookingStates for each Booking, do you want to have all five in the database at the same time? I don't think that makes sense. I would assume you would do something along the lines of inserting to the BookingState as each new booking activity is accomplished. Then you could always get the max value for a given Booking to know what state it's in, but retain history of the various states it has gone through.
Just a guess though.
Instead of trying to explain it to me again, assuming I'm way off, which is very likely, could you post the tables, some sample data and an illustration of what you're trying to do?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 14, 2009 at 4:21 pm
Hi,
Booking Table Schema
BookingID(primary key),
bookingtypeID
.
.
BookingStates Table Schema
StateID (primary key) BookingID(primary key)
States Table Schema
StateID(primary key) StateName(Primary Key)
Booking Data
79
80
81
82
83
84
85
86
87
88
89
90
91
Booking States
179
180
181
182
183
184
185
186
187
188
189
190
191
279
280
282
283
284
285
286
287
288
289
290
379
380
382
383
384
385
386
387
388
389
390
479
480
482
483
484
485
486
487
488
489
490
579
580
582
583
584
585
586
587
588
589
590
679
680
682
683
684
685
686
687
688
689
690
779
780
782
783
784
785
786
787
788
789
790
888
889
890
States
1State1
2State2
3State3
4State4
5State5
6State6
7State7
8State8
Thanks
October 15, 2009 at 5:26 am
I'm sorry, I'm still stuck on what it is that you're trying to do.
Also, because I'm just not going to try to type your code into my system, if you tried posting the table defs & data as outlined in this article[/url], I'm much more likely to use them.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply