SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 43
  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)

    Problem joining separate tables

    Hello everyone,

    could someone please help me with a query?

    I have one large table consisting of a number of foreign key columns.

    The join I've used is like this:

    $q = "SELECT column names
    FROM bigtable AS big INNER JOIN
    table1 AS T1 ON T1.some_id=big.some_id INNER JOIN
    table2 AS T2 ON T2.another_id=big.another_id
    ...WHERE category = '$category'";

    In the above query I'm joining all of the tables to the "bigtable", but now I'd like to also join two other tables in the same query. These two tables only join one another, the one table has a foreign key from the "bigtable" (if this is relevant).

    I tried the following but it doesn't work too well:



    $q = "SELECT column names
    FROM bigtable AS big INNER JOIN
    table1 AS T1 ON T1.some_id=big.some_id INNER JOIN
    table2 AS T2 ON T2.another_id=big.another_id INNER JOIN
    other_table1 AS OT1 INNER JOIN
    other_table2 AS OT2 ON OT2.other_id = OT1.other_id
    ...WHERE category = '$category'";


    I hope this makes sense.


    Thank you very much!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    "the one table has a foreign key from the "bigtable" (if this is relevant)."

    SELECT column names
    FROM bigtable AS big INNER JOIN
    table1 AS T1 ON T1.some_id=big.some_id INNER JOIN
    table2 AS T2 ON T2.another_id=big.another_id INNER JOIN
    other_table1 AS OT1 ON OT1.somekey = big.someFK INNER JOIN
    other_table2 AS OT2 ON OT2.other_id = OT1.other_id

    best i could do, given all the fake names
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    INNER JOIN
    other_table1 AS OT1 INNER JOIN
    You are missing an ON condition here. Unless you want to do a cross join?

  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)
    Hi Rudy and Guido,

    thank you for your assistance. I think it works but I've noticed a problem.


    "the one table has a foreign key from the "bigtable" (if this is relevant)."
    This table with the foreign key is a "lookup" table (or whatever the term is), which means that the same foreign key can appear multiple times in the table. At one point my query loops out some data but it now loops out the same data multiple times. If the foreign key appears 5 times in the lookup table, the data will be looped out 5 times.

    Do you know what I can do to prevent this?


    Thanks!

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RedBishop View Post
    Do you know what I can do to prevent this?
    you could maybe not let your query loop?

    it's really really hard to figure out what you're doing with all those fake table and column names clogging up 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)
    Hi Rudy,

    sorry for the confusion, this is what I'm trying to do:

    Code:
    $q = "SELECT column names
    FROM hotels AS h INNER JOIN
    rooms AS r ON r.hotel_id=h.hotel_id INNER JOIN
    hotels_features AS hf ON hf.hotel_id = h.hotel_id INNER JOIN
    features AS f ON f.features_id = hf.features_id
    ...WHERE category = '$category'";

    I need to loop out the rooms but because the hotel_id appears multiple times in the hotels_features table, the rooms will loop out multiple times.



    Thank you.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RedBishop View Post
    I need to loop out the rooms but because the hotel_id appears multiple times in the hotels_features table, the rooms will loop out multiple times.
    okay, i understand the problem

    but the solution is not to tinker with the query

    you need to write a loop within a loop when processing the query results

    perhaps ask this question in the php forum, or whatever language you're using
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

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

  9. #9
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,266
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Thread moved to PHP to save people from having to jump back and forth.....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  10. #10
    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 everyone,

    it still doesn't work, it seems that the hotel_id in the lookup table is definitely the problem. As a last resort, must I use another query?


    Code:
    $q = "SELECT column names
    FROM hotels AS h INNER JOIN
    rooms AS r ON r.hotel_id=h.hotel_id INNER JOIN
    hotels_features AS hf ON hf.hotel_id = h.hotel_id INNER JOIN
    features AS f ON f.features_id = hf.features_id
    ...WHERE category = '$category'";
    
        $r = @mysqli_query($dbc, $q);   
        $row = mysqli_fetch_array($r, MYSQLI_ASSOC); 
    
    
    // echo data here
    
    
    $r2 = @mysqli_query ($dbc, $q);
    while ($row2 = mysqli_fetch_array($r2, MYSQLI_ASSOC)) { 
    
    // loop content here
    }
    
    // continuing echoing data here

    Thank you!

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RedBishop View Post
    it still doesn't work, it seems that the hotel_id in the lookup table is definitely the problem.
    please tell me you didn't actually run it like that... with "column names" in the SELECT clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  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)
    Of course I don't actually use the words "column names" in my query. I'd have the table alias.column names such as r.room_name, f.features_name etc..

    I know my skills are limited by come on...

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, then

    my skills as a psychic are limited, so please explain what exactly does "doesn't work" mean?
    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)
    Surely you of all people would know what problem I'm talking about?


    I need to loop out the rooms but because the hotel_id appears multiple times in the hotels_features table, the rooms will loop out multiple times.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RedBishop View Post
    Surely you of all people would know what problem I'm talking about?
    not until you tell me

    and FYI, i don't do php, but they tell me it's not all that complicated

    in my application language (coldfusion), i don't have any trouble getting loops within loops to spit out the correct results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think the problem lies with the query, and not with PHP. The query creates a Cartesian product, hence the multiplication.

    But how to avoid the multiplication?

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RedBishop View Post
    But how to avoid the multiplication?
    yeah, i can see the problem now that i stop and think about the probable cardinalities -- a hotel has multiple rooms, and a hotel also has multiple features

    you will have to collapse one of the multiplicities in a subquery

    if i knew the extent of the columns being returned (instead of "column names") then i'd be able to recommend which of them would be easier
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    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've changed my query and am now looping out the hotels, together with the features of every hotel. There are plenty more columns in my hotel table but to simplify things I've only included the hotel name here. The problem now is that the same hotel is looping out however many times the hotel_id appears in the lookup table.

    $q = "SELECT h.hotel_name, f.features_name
    FROM hotels AS h INNER JOIN
    hotels_features AS hf ON hf.hotel_id = h.hotel_id INNER JOIN
    features AS f ON f.features_id = hf.features_id";


    Thanks for your help. This is driving me nuts.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    this query looks correct

    yes, obviously, if a hotel has multiple features, then the hotel name will appear multiple times in the query results

    like i said, i don't do php, but looping over the results and only showing each hotel name once is trivial
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

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

    Perhaps someone else will know how to do this?
    I'm using a standard while loop to loop out all of the hotels:

    while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {




    }

  21. #21
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    733
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Just build the data into a usable array you can then loop through. Here's a sample. Not tested.
    PHP Code:
    <?php  
    $hotels_array 
    =array();
    // SAMPLE Data array building for hotel //
    $q "SELECT 
    h.hotel_id,
    h.hotel_name,
    h.address,
    h.city,
    h.state,
    h.country,
    h.zipcode,
    h.phone,
    r.room_id,
    r.room_name,
    r.room_beds,
    r.room_floor,
    r.room_number,
    r.room_description,
    hf.features_id,
    hf.feature_name,
    f.feature_description
    FROM hotels AS h 
        LEFT JOIN rooms AS r 
            ON r.hotel_id = h.hotel_id 
        LEFT JOIN hotels_features AS hf 
            ON hf.hotel_id = h.hotel_id 
        LEFT JOIN features AS f 
            ON f.features_id = hf.features_id
    WHERE h.category = '
    $category'";

    $r mysqli_query($dbc$q);   
    while(
    $row mysqli_fetch_array($rMYSQLI_ASSOC)){
        
    $hotels_array[$row['hotel_id']]['hotel_name'] = $row['hotel_name'];
        
    $hotels_array[$row['hotel_id']]['address'] = $row['address'];
        
    $hotels_array[$row['hotel_id']]['city'] = $row['city'];
        
    $hotels_array[$row['hotel_id']]['state'] = $row['state'];
        
    $hotels_array[$row['hotel_id']]['country'] = $row['country'];
        
    $hotels_array[$row['hotel_id']]['zipcode'] = $row['zipcode'];
        
    $hotels_array[$row['hotel_id']]['phone'] = $row['phone'];
        
    $hotels_array[$row['hotel_id']]['rooms'][$row['room_id']]['room_name'] = $row['room_name'];
        
    $hotels_array[$row['hotel_id']]['rooms'][$row['room_id']]['room_beds'] = $row['room_beds'];
        
    $hotels_array[$row['hotel_id']]['rooms'][$row['room_id']]['room_floor'] = $row['room_floor'];
        
    $hotels_array[$row['hotel_id']]['rooms'][$row['room_id']]['room_number'] = $row['room_number'];
        
    $hotels_array[$row['hotel_id']]['rooms'][$row['room_id']]['room_description'] = $row['room_description'];
        
    $hotels_array[$row['hotel_id']]['hotels_features'][$row['features_id']]['feature_name'] = $row['feature_name'];
        
    $hotels_array[$row['hotel_id']]['hotels_features'][$row['features_id']]['feature_description'] = $row['feature_description'];
    }  
        
    ////////////////////////////////////////////// 
        //   All data is now in a usable array.     //  
        //   Loop through the parts you want.       // 
        //   $v = value array, just identify key    //
        ////////////////////////////////////////////// 

        
    foreach($hotels_array as $hotel_id => $v){
            
    $hotel_name $v['hotel_name'];
            
    $address $v['address'];
            
    $city $v['city'];
            
    $state $v['state'];
            
    $country $v['country'];
            
    $zipcode $v['zipcode'];
            
    $phone $v['phone'];            
            
    // echo or build display of hotel info here
            
            //Go through rooms array
            
    foreach($v['rooms'] as $room_id => $r){
                
    $room_name $r['room_name'];
                
    $room_beds $r['room_beds'];
                
    $room_floor $r['room_floor'];
                
    $room_number $r['room_number'];
                
    $room_description $r['room_description'];        
                
    // echo or build display of room info here            
            
            
    }
            
    //Go through hotels_features array
            
    foreach($v['hotels_features'] as $feature_id => $f){
                
    $feature_name $r['feature_name'];
                
    $feature_description $r['feature_description'];        
                
    // echo or build display of feature info here            
            
            
    }                
        }
    ?>

  22. #22
    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 there Drummin,

    thank you very much - the code is working perfectly. You sure know your way around arrays. It must have taken some time to write the above code, so thanks again.

    Now that this problem has been sorted out, I see that there's another problem. I'm displaying a certain number of hotels per page, which is why I'm using the COUNT() function to count the number of hotel_ids. But again there's unwanted multiplication of hotel_ids going on. Do you know of a way to just count the hotel_ids in the hotel table?

    If you could please take a moment to look at the query.


    Thank you!


    PHP Code:
    $q "SELECT COUNT(h.hotel_id), other columns...

    FROM hotels AS h 
    INNER JOIN rooms AS r 
    ON r.hotel_id = h.hotel_id 
    INNER JOIN hotels_features AS hf 
    ON hf.hotel_id = h.hotel_id 
    INNER JOIN features AS f 
    ON f.features_id = hf.features_id
    WHERE h.category = '
    $category'"

    $r = @mysqli_query ($dbc$q);
    $row = @mysqli_fetch_array ($rMYSQLI_NUM);
    $records $row[0]; 

  23. #23
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    733
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    And what if you use my query then use
    count($hotels_array);

    What does that show?

  24. #24
    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 for getting back to me.

    And what if you use my query then use
    count($hotels_array);

    What does that show?

    I have done that and it displays 13, which is accurate since there are 13 hotels in the table at present. However, something is still not right. I set the number of hotels to display per page to 3, but there is only 1 hotel showing. If I set the number of hotels to 6, then only 2 hotels will display. Is it possible that the count($hotels_array) is accurate but that hotels take up 3 rows each? Since it is a multidimensional array? If that makes any sense...

    There are 2 queries, the one just counts the number of hotels, and sets the $records variable. The other query is the same except that it fetches the data and includes a LIMIT clause. Currently the LIMIT clause would be LIMIT 0, 3 (to display 3 hotels per page). $start keeps track of the pagination. If the LIMIT clause in the query is the problem then I don't know what to do.

    Sorry to bother you with this, and I really appreciate your help.

    Thanks again.


    I have shortened the code somewhat:

    PHP Code:
     <?php  
    $hotels_array 
    =array();
    // SAMPLE Data array building for hotel //
    $q "SELECT 
    h.hotel_id,
    h.hotel_name,
    h.address,
    h.city,
    h.state,
    h.country,
    h.zipcode,
    h.phone,
    r.room_id,
    r.room_name,
    r.room_beds,
    r.room_floor,
    r.room_number,
    r.room_description,
    hf.features_id,
    hf.feature_name,
    f.feature_description
    FROM hotels AS h 
        LEFT JOIN rooms AS r 
            ON r.hotel_id = h.hotel_id 
        LEFT JOIN hotels_features AS hf 
            ON hf.hotel_id = h.hotel_id 
        LEFT JOIN features AS f 
            ON f.features_id = hf.features_id 
    WHERE h.category = '
    $category' LIMIT $start$display";

    $r mysqli_query($dbc$q);   
    while(
    $row mysqli_fetch_array($rMYSQLI_ASSOC)){
        
    $hotels_array[$row['hotel_id']]['hotel_name'] = $row['hotel_name'];
        
    $hotels_array[$row['hotel_id']]['address'] = $row['address'];
       
    // etc...
    }  
        foreach(
    $hotels_array as $hotel_id => $v){
            
    $hotel_name $v['hotel_name'];
            
    $address $v['address'];
         
                    
    // echo or build display of hotel info here
            
            //Go through rooms array
            
    foreach($v['rooms'] as $room_id => $r){
                
    $room_name $r['room_name'];
                
    $room_beds $r['room_beds'];
     
               
    // echo or build display of room info here   
            
            
    }
            
    //Go through hotels_features array
            
    foreach($v['hotels_features'] as $feature_id => $f){
                
    $feature_name $r['feature_name'];
                
    $feature_description $r['feature_description'];       
     
                
    // echo or build display of feature info here            
            
            
    }                
        }
    ?>

  25. #25
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    689
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Yes, the limit clause is the problem. Each join combination will produce a new row.

    The easiest solution is to remove the limit clause and then just show the first three hotels. Disadvantage of course it that it will always query all hotels and possibly be quite slow.

    Second solution is to break your query in two. The first query just get the hotel id for the first three hotels.

    SELECT hotel_id FROM hotels WHERE hotel.category = '$category' LIMIT $start, $display";

    The do your big join query with

    SELECT blah blah blah WHERE hotel.id IN (id1,id2,id2)

    Third solution - After getting the second solution working and understanding what is going on then it should be possible to go back to a single query using a sub-query. But get the second solution running first. Easier to conceptualize and to debug.


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
  •