Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Generaltao
Starting Member
8 Posts |
Posted - 2004-09-14 : 09:46:03
|
Okay, I ran into another snag here. This is still the same car leasing database. Now, my requirement is that I have to build a select statement that shows a list of vehicles with descriptions but also follow it with the largest maximum kilometers that was ever allowed in a lease terms for that vehicle.Each Lease is linked to a table of set 'lease terms'. In this Terms table there is a field called TermMaxKM. Somehow I have to:A)Link up vehicles table to the three description look-up tables to retrieve proper descriptions (Colour, Type, and Model tables)B)Have Vehicles joined to their related leases (which they could potentially have many in their history.)C) Join these Leases to the Terms table through the TermID field so that I can get the max KM for that termD)Display only the HIGHEST VALUE of max KM from any lease that was issued for a vehicle.My brain kind of hurts right now. I have the database diagram here, but my brain is having a hard time trying to figure out where to go from here. This is my groundwork, though it might be entirely off base:quote: SELECT Vehicles.VIN, Model_Desc = (Model.ModelDesc), Type_Desc = (Type.TypeDesc), Colour_Desc = (Colours.ColourDesc), Max_KM = (SELECT max(Terms.termmaxkm) FROM [Terms] WHERE Lease.VIN = Vehicles.VIN)FROM Lease INNER JOIN TermsON Lease.TermID = Terms.TermIDINNER JOIN VehiclesON Lease.VIN = Vehicles.VININNER JOIN Modelon Vehicles.ModelID = Model.ModelIDINNER JOIN Type ON Vehicles.TypeID = Type.TypeIDINNER JOIN ColoursON Vehicles.ColourID = Colours.ColourIDWHERE Lease.TermID = Terms.TermID
This might be more of a logic problem than a technical one, but any help would be greatly appreciated. |
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-09-14 : 10:06:11
|
Keep A seperate from B,C, and D. A has no bearing on the logic of the SELECT statement. disregard for now, until you can get the math to work out right.If the goal is to return 1 row per VIN, then you know that at some point you will need to group by VIN in a subquery, and for each VIN you need to return the MaxKM. Strip away everything else, and figure out how to return that result. From what I can gather, I think it is this:select V.VIN, MAX(TermMaxKM) as MaxKMfrom Vehicles Vinner join Lease L on V.VIN = L.VINinner join Terms T on L.TermID = L.TermID Test it, troubleshoot it, make sure it makes sense before moving on. remember, the goal for this 1 step is to return the MAX KM per VIN. when you 100% understand that code and get it to work, then you work on the next steps.The next steps, then, are just to join the above SQL to your other tables and voila -- you have your final result:(this is modified from the SQL you've posted) :SELECT Vehicles.VIN, Model_Desc = (Model.ModelDesc), Type_Desc = (Type.TypeDesc), Colour_Desc = (Colours.ColourDesc), B.MaxKMFROM Lease INNER JOIN TermsON Lease.TermID = Terms.TermIDINNER JOIN VehiclesON Lease.VIN = Vehicles.VINFROM VehiclesINNER JOIN Modelon Vehicles.ModelID = Model.ModelIDINNER JOIN Type ON Vehicles.TypeID = Type.TypeIDINNER JOIN ColoursON Vehicles.ColourID = Colours.ColourIDINNER JOIN (the above SQL) B on Vehicles.VIN = B.VINWHERE Lease.TermID = Terms.TermID WHen you build the above, add 1 table at a time, make sure it works, add the next. work on it step by step. too many people throw 15 tables together in a join all at once, and then when it doesn't work they randomly added GROUP BY's and WHERE clauses and all that w/o knowing what is going on. 1 step at a time, building from the inside out.- Jeff |
|
|
Generaltao
Starting Member
8 Posts |
Posted - 2004-09-14 : 10:09:57
|
Thank you very much! I'm trying to wrap my brain around the logic since database structure logic and database programming is all foreign to me. I feel like I'm being tossed from the nest without the power to fly yet!I hope this works. Thanks for taking the time to answer my question |
|
|
|
|
|
|
|