CAN U SOLVE MY QUERY

Question

CAN U SOLVE MY QUERY

sanjeev-218769

Grasshopper

Points: 21
More actions
June 11, 2007 at 6:42 am

#174074

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 1

You probaby would get a faster response if you posted in one of the T-SQL sections. However, assuming your range field is varchar and looks something like '100-200' the following should work:

select t1.state, t2.hotelname, t1.range

from (select c.state, min(h.range) range

from hotel h join city c on (h.city = c.city)

group by c.state) t1 join

(select c.state, c.city, h.hotelname, h.range

from hotel h join city c on (h.city = c.city)

) t2 on (t1.state = t2.state and t1.range = t2.range)

order by t1.state,t2.hotelname

--James.

sanjeev-218769 Grasshopper Points: 21 More actions · Answer 2

Thankyou James for solving my problem. You are simply great with brilliant brain . Thats why i like this forum.

Thanks,

Cheers

Sanjeev

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

James,

You forgot to ask, "What have you tried?" and I think you just did someone's homework for him ... the OP cross posted this request and was told the same, but, apparently, didn't get the hint. I don't have a problem helping people with their homework, but I do like to seem them try something on their own first... that we, they learn something other than how to be successful based soley on the kindness of others

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 4

Jeff: I agree, if I had known it was home work I would have tried to give hints rather than the solution. I don't know why it didn't occur to me that this might be a homework assignment but it never even crossed my mind. The only thing I was thinking of when working the solution was "What a crappy database design!"

James.