CAN U SOLVE MY SQL PROBLEM
-
I HAVE TWO TABLES
HOTEL & CITY
IN CITY FIELDS ARE
STATE,CITY
IN HOTEL FIELDS ARE
CITY, HOTELNAME,RANGE
I WANT TO DISPLAY STATE,HOTELNAME,PRICE WHICH HAVE MINIMUM HOTEL PRICE RANGE IN THAT STATE.
STATE,HOTELNAME,RANGE
REPLY ASAP
CHEERS
-
Something along the lines of:
SELECT c.State, h.HotelName, h.MinPrice
FROM City c
INNER JOIN Hotel h
ON c.City = h.City
AND h.Range = (SELECT MIN(Range)
FROM Hotel h2
WHERE h2.City = h.City
and h2.Hotel = h.Hotel
ORDER BY Range ASC)
Adjust where necessary, but this will return the minimum value for each unique city, hotel pair, which is what I think you asked for.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Sanjeev,
Two bits of advice for you.
1. Do not use all uppercase. ALL UPPERCASE INDICATES SHOUTING. We don't like being shouted at
2. Show us what code you have come up with. Then we can show you where you went wrong and help you fix the code. We aren't here to do all of your work for you.
-SQLBill
-
Sanjeev,
Only you can solve your problem by learning SQL. This isn't a place to get others to do your work for you. There are tons of great resources on this here and on many other sites. If you have questions about certain topics or don't understand how things work feel free to post, otherwise don't.
/bah
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
very much agree on the all caps. so why is it that so many sql devs write the keywords such as SELECT in ALL CAPS?
when it comes to sql it seems many dbas and devs like to SHOUT!
---------------------------------------
elsasoft.org -
Jezemine: Is that a serious question? If it is I think I'm beginning to feel my age. The reason for CAPS in any code use to be to indicate reserved words of the language. This helped distinguish betwen the language specific key words and the custom code. It use to be a standard in a lot of programming shops, and the way you learn something is how you tend to continue doing it. Now a days of course we have very smart development editors that color code the syntax and set reserved words one color and other stuff a different color. While I don't do it as much as I use to, I do find that in my production code I still capalize the reserved words.
James.
-
I still capitalize the reserved words because not all our SQL code will end up in a nice graphical editor. It does make it a bit easier to read.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Yes, it was a serious question. I always figured it was a throwback to the days when monitors could only display upcase chars. At least that's all my apple II+ could do back in the day...
I've been programming in C/C++ (and now mostly C#) for many years, but in SQL only 6 years or so, since 2000 came out, so I don't have a very long history with database programming like some regulars here I'm sure.
I don't think caps makes it easier to read - just looks like shouting to me. Plus it's a real PITA (or perhaps PITH) to type a mixture of all caps and lowercase words. I'm sure glad that the keywords in case-sensitive languages I use aren't ALL CAPS.
---------------------------------------
elsasoft.org -
I do have to admit to using Redgate SQL Refactor & SQL Prompt. Between the two they do tons of formatting so I don't have to. I do like the look of well formed SQL code, but I'm a lazy b'tard when it comes to all that typing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Jezemin: I hope I didn't insult you, but I just wasn't sure if you were being facetious. I guess I really am feeling my age
I've worked with many languages over the years (Fortran, RPG, Cobol, C, C++, Pascal, VB, SQL, Informix) and any that were not case sensitive it seems had standards that capatilized the reserve words. Though I never asked anyone why, just went with the flow, I always assumed (and you know what happens when you assume) it was for read ability (back then we printed the source code a lot) and to assist with identifying the built in functions versus functions/procedures that were custom code. Guess maybe I should have asked more questions when I was younger and maybe wouldn't look so ignorant now
Since it was a serious question, I hope we have answered it for you.
James.
-
no worries, I'm not insulted at all!
and yes you did answer it, thanks.
---------------------------------------
elsasoft.org -
POOR SANJEEV..
-
Sanjev... come on... let us know your your query. If you would have given some draft query this topic would have taken to different direction...
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply