SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    multi join query troubles

    Hi,

    The following query works fine until I add the join on the image_galleries table. What it then does, is return some of the 'images' and 'accompanying text' - (for 4 images instead of all seven). It outputs that data repeatedly for each image/text; the same number of times as there are facilities.

    I notice also that the the array of data returned for the images and text, it chopped off such that the fifth array element stops midway through. ie image 2 outputs 5 times as does image 3 and image 4. BUt on the fourth output of image 4, the output is cut off midway through the text.

    q1. what I have overlooked in my query?
    q2. is the 'cutting off' caused by a memeory issue of some sort or is it all down to my bad query?

    Any guidance greatfully received.

    Code MySQL:
    SELECT 
          ps.room_type
            , ps.description
            , GROUP_CONCAT(
                      CONCAT_WS( ','
                       , fp.facility_category
                       , fp.facility_name
                       ) ORDER BY fp.facility_category asc
              SEPARATOR ';' ) AS facilities
            , GROUP_CONCAT(
                      CONCAT_WS( ','
                        , im.image
                        , im.accompanying_text
                      ) ORDER BY im.image asc
                SEPARATOR ';' ) AS images
       from  products_stock ps
     inner 
        join central_DB.image_galleries as im
         on im.business_id = ps.business_id
       and im.gallery_name = ?
       and im.active = 1 
       and im.gallery_name = ps.room_type
     
     left outer
      JOIN facilities_products as fp
        ON fp.live_product_id = ps.id
       AND fp.business_id = ps.business_id
     inner
       join product_live pl 
        on pl.product_id = ps.id
      and pl.start_date <= curdate()
      and pl.end_date > curdate() 
      and priority = 'b'
     
    where ps.business_id = ?  
    and ps.room_type = ?

    bazz

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    It outputs that data repeatedly for each image/text; the same number of times as there are facilities.
    i call this "cross join effects"

    while not a true cross join, it exhibits the same behaviour within each grouping of the main entity (in your case, for each property)

    it is the result of combining two one-to-many relationships off the same entity

    the way to fix this is to choose one of the relationships, and, instead of joining directly to it (resulting in multiple rows), join instead to a subquery which contains a GROUP BY -- in mysql, you can easily bring forth the multiples of the "many" side of the relationship by using GROUP_CONCAT in that subquery -- so that you are joining to only one row

    to relate this to your query, instead of doing the GROUP_CONCATs in the outer query, do one of them inside a subquery, on, say, the images
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy,

    I'll try that shortly.

    bazz

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Uh-oh! This doesn't bring me any results and it is because of something in the derived table part of the query.

    What am I not getting? this is becoming frustrating.


    bazz

    Code MySQL:
     
    SELECT ps.business_id
             , ps.room_type
             , ps.description
             , GROUP_CONCAT(
                     CONCAT_WS( ','
                        , fp.facility_category
                        , fp.facility_name
                                       ) ORDER BY fp.facility_category asc
                 SEPARATOR ';' ) AS facilities
              , im.image_data
         from
           bookings.products_stock ps
    left outer
         JOIN bookings.facilities_products as fp
           ON fp.live_product_id = ps.id
          AND fp.business_id = ps.business_id
      inner
          JOIN ( SELECT business_id
                 , gallery_name
                 , active
                 , GROUP_CONCAT(
                           CONCAT_WS(','
                               , image
                               , accompanying_text
                           )
                     SEPARATOR ';' ) AS image_data
           FROM central_DB.image_galleries 
                   ) AS im
                 ON im.business_id = '153'
                and im.gallery_name = 'Double (Standard)' 
     
           where ps.business_id = '153'  
              and ps.room_type = 'Double (Standard)'

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I am getting a better response from the query but it still cuts off midway through one of the results. Could this be for some reason other than a bad query?

    here is what is being returned. As I hope is clear, the room2_6.jpg text is not complete and the image and data for room2_7.jpg is completely missing.

    The data for image2_7.jpg and its text, is in keeping with what the query is looking for.

    bazz
    Code:
    $VAR1 = [
              'room2_2.jpg,We have completely re-furnished this room, including with a new bed and mattress, for April 2010. ',
              'room2_3.jpg,The period feature fireplace in The Large Double. ',
              'room2_4.jpg,Space aplenty in our large double. ',
              'room2_5.jpg,Fresh, luxury mono-grammed towelling, including toothpastes, shampoos and shower gels.  ',
              'room2_6.jpg,More th'
            ];
    here is the query:

    Code MySQL:
    SELECT ps.business_id
              , ps.room_type
              , ps.description
             , GROUP_CONCAT(
         CONCAT_WS( ','
            , fp.facility_category
          , fp.facility_name
         ) ORDER BY fp.facility_category asc
                 SEPARATOR ';' ) AS facilities
                           , im.image_data
        from products_stock ps
     left outer
      JOIN facilities_products as fp
        ON fp.live_product_id = ps.id
      AND fp.business_id = ps.business_id
    inner
      JOIN ( SELECT business_id 
                        , gallery_name
                        , GROUP_CONCAT(
                                    CONCAT_WS(','
                               , image
                               , accompanying_text
                          )
                           SEPARATOR ';' ) AS image_data
             from central_DB.image_galleries
           where business_id = '153'
              and gallery_name = 'Double (Standard)' 
              and active = 1
             ) as im   
             on im.business_id = fp.business_id                           
    inner
       join product_live pl
        on pl.product_id = ps.id
       and pl.start_date <= curdate()
       and pl.end_date > curdate() 
       and priority = 'b'
     
    where ps.business_id = '153'  
       and ps.room_type = 'Double (Standard)


    bazz

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, it 'seems' to be a memory/storage issue. If i remove the text from one or two images, the whole string is returned. It seems that what I am trying to return, is more than is being held in the array.

    I'll look up 'memory' and 'storage' but, if there is another issue I may have stumbled across, please let me know

    bazz

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still no success.

    Query 'works' but, it cuts off the results.

    google ain't helping.

    Code MySQL:
    SELECT ps.business_id
             , ps.room_type
             , ps.description
             , im.image_data
             , GROUP_CONCAT(
                       CONCAT_WS( ','
                         , fp.facility_category
                         , fp.facility_name
                ) ORDER BY fp.facility_category asc
                 SEPARATOR ';' ) AS facilities
          from bookings.products_stock ps
        left outer
          JOIN bookings.facilities_products as fp
            ON fp.live_product_id = ps.id
          AND fp.business_id = ps.business_id
        INNER
          JOIN ( SELECT business_id
                  , GROUP_CONCAT(
                       CONCAT_WS(','
                             , image
                             , accompanying_text
                       ) 
                     SEPARATOR ';' ) AS image_data
             FROM central_DB.image_galleries
              where gallery_name = ?
                and active = 1
             group by gallery_name
             ) as im   
             on im.business_id = ps.business_id            
     
           inner
             join bookings.product_live pl  
               on pl.product_id = ps.id
             and pl.start_date <= curdate()
             and pl.end_date > curdate() 
             and priority = 'b'
     
          where ps.business_id = ?  
             and ps.room_type = ?

  8. #8
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It seems that the result is limited in some way, to be 300 characters in length.

    If I remove text from some images and run the query, it will show me more of what the query asks for again, limited to 300 chars. Never experienced this before with some much larger result sets.

    bazz

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    bazz, i'm waiting for you to run out of steam

    you are actually doing really well, documenting your progress for our benefit, as you progress through the various stages of debugging

    but until you stop posting, i'm just gonna sit back and watch

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

  10. #10
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am now bald No matter what I seem to try, I always gravitate back to a derived sub select. To say I am now flailing about, is an understatement.

    If you can give me a pointer or search term, I'll try to make another inch of progress. I think I am that close. All the data I want is being returned, provided, it seems, the total size of it is less than 301 characters.

    bazz

  11. #11
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,135
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    GROUP_CONCAT max length

  12. #12
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, I'll read up on that but, I am already confused.

    I have three group_concats in different queries and all of them return more data than this particular one. So I am confised as to why I may need to change a server setting for this one. (Not doubting you oddz! but just confused). I always find the flow of questions much quicker than my ability to answer/learn the answer.



    bazz

  13. #13
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,135
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Its a partial observation based primarily on putting the words truncate and group concatenate together. I haven't reviewed your query in-depth to any manor but it may be more practical to handle the organization of data in application language rather than using group concatenate since its returning truncated results it seems. Which is an indicator that your data is exceeding the max length. At least to me anyway. I know that QueryBrowser will tell you if the result set has been truncated. Perhaps there is a warning you an check for that will tell you if this is truly your problem if not using QueryBrowser.

  14. #14
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oddz, thank you.

    I shall mull over a way to bring in the necessary data without a group_concat. Unless I really must, I would rather not change the server config.

    bazz

  15. #15
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,135
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Try this out:

    Code SQL:
    USE INFORMATION_SCHEMA;
     SELECT * FROM GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'GROUP_CONCAT_MAX_LEN';

  16. #16
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks oddz. gonna take a break coz I can't even get that to work in a query. Grrr!

    bazz

  17. #17
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, I have run that query on every table and all return zero results.

    I had to modify the query several times to run it on all the tables. I don't have a table called GLOBAL_VARIABLES but, instead have a table COLUMNS with a column called GLOBAL_VARIABLES.

    I'll try again, later this morning, after some sleep.

    bazz

  18. #18
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And, now, I am most definitely out of steam

    I have moved away from group_concat in an effort to try to get the same data via an outer join. Only getting one image so far and I am not seeing how a outer join doesn't get me back all images and their captions.

    Code MySQL:
    SELECT ps.business_id
             , ps.room_type
             , ps.description
             , img_data.image
             , img_data.accompanying_text
             , GROUP_CONCAT(
                       CONCAT_WS( ','
                         , fp.facility_category
                         , fp.facility_name
                       ) ORDER BY fp.facility_category asc
                SEPARATOR ';' ) AS facilities
        from bookings.products_stock ps
    left outer
        JOIN bookings.facilities_products as fp
          ON fp.live_product_id = ps.id
        AND fp.business_id = ps.business_id
    left outer
         join (select business_id
                       , image
                       , accompanying_text
                FROM central_DB.image_galleries
                where gallery_name = ?
                   and active = 1
              ) as img_data
          on img_data.business_id = ps.business_id
     
    inner
        join bookings.product_live pl  
          on pl.product_id = ps.id
         and pl.start_date <= curdate()
         and pl.end_date > curdate() 
         and priority = 'b'
     
      where ps.business_id = ?  
         and ps.room_type = ?

  19. #19
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,135
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Having group_concat w/o a group clause is going to return a single row. I don't believe you need that subquery either. Also, that inner join following those left outer joins is turning your left outer joins into inner joins I do so believe. Placing an inner join after a left outer join doesn't mean require this if and only if the previous is available. Also, the <= and >= comparison in the on clause is going to reek hell upon your optimization and scale horribly.

  20. #20
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you both for your help. At long last, (I seem to need everything done NOW, so it always takes me too long) lol, I have the server configured and the page working. Amended the /etc/my.cnf file.
    Now, if / when, the server is shut down and restarted, the change will be read/loaded automatically.

    bazz


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
  •