SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: Mysql Query

  1. #1
    SitePoint Enthusiast t2india's Avatar
    Join Date
    Aug 2003
    Location
    India
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql Query

    Hello,


    I have a table check_hotel in MySql database few records are as follows

    id htlId name country rate

    1 23 Grand China, Taiwan 40
    2 23 Grand General 45
    3 23 Grand USA, Canada 44
    4 34 Park General 50
    5 26 Hyatt General 55
    6 26 Hyatt China 50
    7 26 Hyatt Taiwan 52

    Can I use any query like if country is China it will show id 1, 4, 6. Since htlId = 34 doesn't have specific rate for China. If country is Taiwan then id will be 1, 4, 7.
    Sql Server query can be like this
    /////////
    (Select id from check_hotel where country="General"
    MINUS
    Select id from check_hotel where country like '%China%')

    UNION

    Select id from check_hotel where country like '%China%'
    ///////////

    please suggest me the sql query to get the result
    Travelling to India visit t2india.com
    All India travel

  2. #2
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    rather than suggesting a query I would like to suggest that you take a look at your table design.

    If you have a htlId I would assume that you have a table with all the primary data for all your hotels. So you don't have to include the name in your check_hotel table again. In your primary table you may as well store the general rate for that hotel.

    Then your check_hotel would only contain columns htlId, country, rate and (maybe) id. And please do not put two countries in the name column of one single row. This is not a good idea for future queries. Put them in two different rows, i.e. one for USA and one Canada even if their rates are the same. You are making your life much easier.

    After that small re-design you should be able to get what you want with one query without any UNIONs and without any LIKEs and without any leading %s.
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  3. #3
    SitePoint Enthusiast t2india's Avatar
    Join Date
    Aug 2003
    Location
    India
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello Kleineme,
    Thanks for your suggestion. In primary table I am keeping all the information about the hotel like its description, location, email, fax etc. In hotel rate table please take a look at hotel rate table

    columns in rate table are
    id
    htlId
    room_type
    country
    rate_valid_from
    rate_valid_to
    rate1
    rate2
    rate3
    ...

    Some hotels gives country specific rates, while showing hotels to a visitor of particular country say USA, I want to show all the hotels even if it doesn't have specific rate for USA, but rate should be country specific if exists.
    I have to maintain different table for general market rates also as same hotel may have different room types and different rate validity periods.

    You may get the complete picture if you check this link http://www.t2india.com/fixed-package...n_triangle.inc
    and click Check Pricing button.

    Shall I make one more table which will have only country specific rates??

    Thanks again for your valuable suggestion to not keep two country name in country column.
    Travelling to India visit t2india.com
    All India travel

  4. #4
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Perhaps I'm missing something, but isn't
    A minus B union B
    the same as A ?

    What wrong with
    select * from check_hotel
    where country like '%CHINA%'
    or country = 'GENERAL'

    That should give the result you say you want.

  5. #5
    SitePoint Enthusiast t2india's Avatar
    Join Date
    Aug 2003
    Location
    India
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select * from check_hotel
    where country like '%CHINA%'
    or country = 'GENERAL'
    Will return all the records having country as General or China. But I want if there is a special rate for Country show that or show the rate for general market.

    as in this case
    //////////
    1 | 23 | Grand | China, Taiwan | 40
    2 | 23 | Grand | General | 45
    3 | 23 | Grand | USA, Canada | 44
    4 | 34 | Park | General | 50
    5 | 26 | Hyatt | General |55
    6 | 26 | Hyatt | China |50
    7 | 26 | Hyatt | Taiwan |52
    //////////

    for country as China it should return id: 1, 4 and 6
    and for USA it will be id: 3, 4, 5
    Travelling to India visit t2india.com
    All India travel

  6. #6
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Clearly I AM missing something. I see no reason why if you want china you should want id = 4 and NOT want id = 2 or 5.
    Similarly if you want usa you should expect 4 and 5 but not 2.

  7. #7
    SitePoint Enthusiast t2india's Avatar
    Join Date
    Aug 2003
    Location
    India
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello John,
    We have a travel website. We provide service for Hotel Bookings in India. There are few hotels which gives country specific rates.
    Please check the columns in table there is htlId field (2nd Column).

    Let’s say in Case of China:
    htlId: 23 has special rate for China so show rates having id=1.

    htlId: 34 Doesn’t have special rate for China so show rates valid for all country i.e. 'General' having id=4

    htlId: 26 again have special rate for China so show the rates having id = 6

    So if the site visitor is from China our system should so the rates for id: 1, 4 and 6.

    Similarly for USA id will be: 3, 4, and 5

    Probably this time I am able to clear my points.
    Expecting some one will help me for this soon!
    Thanks
    Travelling to India visit t2india.com
    All India travel

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    first, you should rename the htlId column to remove the special character

    does every htlId have a General entry?

    if so,
    Code:
    select genl.id
         , genl.`ht|Id`
         , coalesce(ctry.room_type, 
                    genl.room_type)        as room_type
         , coalesce(ctry.country, 
                    genl.country)          as country
         , coalesce(ctry.rate_valid_from, 
                    genl.rate_valid_from)  as rate_valid_from
         , coalesce(ctry.rate_valid_to, 
                    genl.rate_valid_to)    as rate_valid_to
         , coalesce(ctry.rate1, 
                    genl.rate1)            as rate1
         , coalesce(ctry.rate2, 
                    genl.rate2)            as rate2
         , coalesce(ctry.rate3, 
                    genl.rate3)            as rate3
      from check_hotel     as genl
    left outer
      join check_hotel     as ctry
        on genl.`ht|Id`
         = ctry.`ht|Id`
       and ctry.country = 'China'
     where genl.country = 'General'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast t2india's Avatar
    Join Date
    Aug 2003
    Location
    India
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great!!!
    It works. THANKS
    No special character in htlId (l is small L)
    Travelling to India visit t2india.com
    All India travel

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by t2india
    No special character in htlId (l is small L)
    doh!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •