SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Junction table query

    Hi everyone,

    I'm using a junction table which holds the columns hotel_ids and features_ids.

    In order to filter the hotels according to the chosen features, I'm using a WHERE clause such as WHERE f.features_name='room service'. But what must I do if there are multiple features? Must I use AND or OR, such as WHERE f.features_name='room service' OR f.features_name='internet access'? The main thing is that only those hotels that have all of the chosen features are returned.


    I am stumped. Thank you in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT h.hotel_id
         , h.hotel_name
         , h.other_hotel_stuff
         , GROUP_CONCAT(f.features.name) AS features
      FROM ( SELECT hf.hotel_id
               FROM features  
             INNER
               JOIN hotel_features 
                 ON hotel_features.features_id = features.features_id
              WHERE features.features_name IN 
                    ( 'room service' 
                    , 'internet access' ) /* note 2 features listed */
             GROUP
                 BY hotel_features.hotel_id       
             HAVING COUNT(*) = 2      /* note 2 features must be counted */
           ) AS x
    INNER
      JOIN hotels AS h
        ON h.hotel_id = x.hotel_id
    INNER
      JOIN hotel_features AS hf
        ON hf.hotel_id = h.hotel_id
    INNER
      JOIN features AS f
        ON f.features_id = hf.features_id
    GROUP
        BY h.hotel_id
    GROUP_CONCAT is, of course, a mysql proprietary function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Brilliant Rudy,

    it's working. Thank you very much! The query is rather complex so I'm glad I asked. You've saved me a few hours looking stupidly at the PC and wondering what to do next.

  4. #4
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello Rudy,

    how are you doing?

    The query you gave me is working well, thanks. What would I have to change if I wanted to add another intermediary table "hotel_activities" to the mix? I thought I'd ask.


    Thank you!

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RedBishop View Post
    What would I have to change if I wanted to add another intermediary table "hotel_activities" to the mix?
    same type of subquery as subquery "x"

    you need the new subquery to produce one result per hotel_id, then join that to the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, will try that out.

  7. #7
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    sorry to bother you with this but could you please have a quick look at my query?

    I have used the same columns and tables for the second GROUP_CONCAT as I used for the first GROUP_CONCAT, adding a comment here and there. This is just so one can see the structure of the query. Possibly the INNER JOINS need some amending?

    I know you said:

    you need the new subquery to produce one result per hotel_id, then join that to the query

    Thank you!!!


    Code:
    SELECT h.hotel_id
         , h.hotel_name
         , h.other_hotel_stuff
         , GROUP_CONCAT(f.features.name) AS features
      FROM ( SELECT hf.hotel_id
               FROM features  
             INNER
               JOIN hotel_features 
                 ON hotel_features.features_id = features.features_id
              WHERE features.features_name IN 
                    ( 'room service' 
                    , 'internet access' ) 
             GROUP
                 BY hotel_features.hotel_id       
             HAVING COUNT(*) = 2     
           ) AS x, // added a comma here
           
           GROUP_CONCAT(f.features.name) AS features
      FROM ( SELECT hf.hotel_id
               FROM features  
             INNER
               JOIN hotel_features 
                 ON hotel_features.features_id = features.features_id
              WHERE features.features_name IN 
                    ( 'room service' 
                    , 'internet access' ) 
             GROUP
                 BY hotel_features.hotel_id       
             HAVING COUNT(*) = 2      
           ) AS xx // added an x here
      
    INNER
      JOIN hotels AS h
        ON h.hotel_id = x.hotel_id  // inner join from first group_concat
        
    INNER
      JOIN hotels AS h
        ON h.hotel_id = xx.hotel_id   // inner join from second group_concat
        
    INNER
      JOIN hotel_features AS hf
        ON hf.hotel_id = h.hotel_id
    INNER
      JOIN features AS f
        ON f.features_id = hf.features_id
    GROUP
        BY h.hotel_id

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    whole bunch of things wrong with your query -- you cannot add a column in the middle of the FROM clause, you cannot add a second FROM keyword, and your second subquery is identical to the first, going after 'room service' and 'internet access' as qualifying features

    so i've re-thought the query and made a couple of changes

    i've changed the first subquery to retrieve all features for each hotel, not via the main query, and included code to detect when the features include all the ones that were specified as qualifying

    then i've added a second subquery, to retrieve all amenities for each qualified hotel

    thus the outer query doesn't need a GROUP BY any more

    Code:
    SELECT h.hotel_id
         , h.hotel_name
         , h.other_hotel_stuff
         , x.features
         , xx.amentities
      FROM hotels AS h
    INNER
      JOIN ( SELECT hotel_features.hotel_id
                  , GROUP_CONCAT(features.features_name) AS features
                  , SUM(CASE WHEN features.features_name 
                               IN ( 'room service' 
                                ,  'internet access' ) 
                             THEN 1 ELSE NULL END ) AS qualifying
               FROM hotel_features  
             INNER
               JOIN features 
                 ON features.features_id = hotel_features.features_id
             GROUP
                 BY hotel_features.hotel_id       
           ) AS x
        ON x.hotel_id = h.hotel_id  
       AND x.qualifying = 2       
    INNER
      JOIN ( SELECT hotel_amenities.hotel_id
                  , GROUP_CONCAT(amenities.amenities_name) AS amenities
               FROM hotel_amenities
             INNER
               JOIN amenities
                 ON amenities.amenities_id = hotel_amenities.amenities_id
             GROUP
                 BY hotel_amenities.hotel_id       
           ) AS xx 
        ON xx.hotel_id = h.hotel_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    thanks a lot.

    I have to ask because I haven't tried the code yet, but this is intended to work with 2 intermediate tables? The one table I have is for amenities and the other table for activities - each hotel can have multiple amenities and multiple activities.

    Just checking - thank you!

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RedBishop View Post
    The one table I have is for amenities and the other table for activities - each hotel can have multiple amenities and multiple activities.
    dude, what happened to features?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    dude, what happened to features?

    Aren't features the same as amenities? I definitely have an amenities/features table and now I tried adding another intermediate table which I arbitrarily called "activities".

  12. #12
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    I have to run, but I'll be back later.

    If you could please confirm that the code will work for 2 intermediate tables?

    Thanks!

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RedBishop View Post
    If you could please confirm that the code will work for 2 intermediate tables?
    um, that'd be your job?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    I am happy to report that your code does work! Thank you for helping me with this. That's one problem solved, another 1000 to go... It seems that solving one problem results in another problem... it's a vicious cycle.


    Thanks again


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
  •