SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    country
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    single query instead of multiple to achieve the same

    Hi

    I hope i'll manage to explain what i'm trying to do

    i'm having two related tables.
    first table holds galeries and second table holds images.
    So multiple images in second table are associated with single galery in first table (one to many relation)

    I want to display N galleries per page and their images simulteniously.

    for example:

    Code:
    galeries table:
    
    
    row #      gallery_id                  gallery_name            
    -------------------------------------------------------- 
      1                  2                          Storms
      2                  3                             Sea
    
    
    
    
    images table:
    
    row #          image_galleryId                image_id            image_name
    -----------------------------------------------------------------------------
      1                     3                             1                    waves
      2                     3                             2                  fisherman
      3                     2                             3                     wind
      4                     3                             4                    offshore
      5                     2                             5                fallen tree

    i would like to display this as:

    Code:
    gallery_name          image_name
    --------------------------------------
        Storms                     waves
        Storms                   fisherman
        Storms                     offshore
        Sea                           wind
        Sea                         fallen tree

    the problem is that i want to display say 5 gallery records per page and i don't know ahead how many images there are in each gallery...
    so if i use query like:

    PHP Code:
    "SELECT gallery_name,image_name FROM gallery,image
      WHERE gallery_id=image_galleryId LIMIT "
    .$start.",5" 
    i get 5 records but the actual number of distinct galleries records are less than 5(it is equal to 5 only when each gallery contains exactly one image).
    I realize that i could achieve that by 2 queries:
    first one:
    PHP Code:
    $query="SELECT gallery_name,gallery_id FROM gallery LIMIT ".$start.",5"
    $res=mysql_query($qr); 
    and then i would loop over retrieved gallery records with query like:
    PHP Code:
    while($row=mysql_fetch_assoc($res))
    {
         
    $query="SELECT image_name FROM image WHERE image_galleryId='".$row['gallery_id']."'";
        
    $res1=mysql_query($query);
    // and then inner loop to print the data
        
    while($row1=mysql_fetch_assoc($res1))
             echo(
    $row['gallery_name'].'               '.$row1['image_name']);

    (i hope i was clear...)

    Is it possible to achieve the same with single db query?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm not certain it's possible, but it would be something like this --
    Code:
    select gallery_name
         , image_name 
      from gallery
    inner
      join image 
        on gallery_id
         = image_galleryId 
       and image_galleryId in
           ( 
           select gallery_id 
             from gallery as X
            where ( 
                  select count(*) 
                    from gallery
                   where gallery_id > X.gallery_id ) 
               between ".$start." and ".$start." + 5 
           )
    i do know that if you try this --
    Code:
    select gallery_name
         , image_name 
      from gallery
    inner
      join image 
        on gallery_id
         = image_galleryId 
       and image_galleryId in
          ( SELECT gallery_id 
              FROM gallery 
            order by gallery_id limit ".$start.", 5 )
    then you will get the following error message in 4.1 --
    This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    country
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but you using subquery... it is not supported yet on most hosts

    if it is not possible i guess i would proceed as i did in example....
    thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if you are not yet on 4.1, you should have said so

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

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    country
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    at my home i do have 4.1 but my host is still using 4.0

  6. #6
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    country
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    ...
    image_galleryId in
    ( 
           select gallery_id 
             from gallery as X
            where ( 
                  select count(*) 
                    from gallery
                   where gallery_id > X.gallery_id ) 
               between ".$start." and ".$start." + 5 
    )
    probably i don't understand the query but something seems not right...
    inside in() clause you have nothing refering to image_galleryId but you use it outside of in()...
    is it correct?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    inside the IN() subquery, i am retrieving gallery_IDs of the galleries, where these galleries are selected based on a range using $start, instead of a LIMIT

    outside the IN() subquery, i select images and galleries based on those gallery_IDs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    country
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    considering that i only need to retrive 20 gallery records per time, what is more efficient/faster in this case, to do one query with several subqueries or do what i did in example ?

    meanwhile i'll study your example

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    20 gallery records means you are doing 20 queries inside a loop? yikes!

    what do you mean, "one query with several subqueries"??
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    country
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what do you mean, "one query with several subqueries"??
    your query contain several subqueries.

    20 gallery records means you are doing 20 queries inside a loop? yikes!
    but there is no other options other that making my host to upgrade db...
    and i read that sometimes making several small queries is faster, because can be optimized, than making one comlex one... so i thought that maybe in my case it might be true

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, yes, but you really only need 2 queries, and the 2nd one is not in a loop

    first query gets gallery_IDs, second query gets all images for those galleries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    country
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, finally i got you...instead me having to loop, you say, let mysql loop through SET() .
    i should do something like this:
    PHP Code:
    $query="SELECT gallery_id FROM gallery LIMIT ".$start.",".$numOfRecs;
    $res=mysql_query($query);
    // this selects galleries in the desired range...

    $ids=array();
    while(
    $row=mysql_fetch_assoc($res))
    {
         
    $ids[]=$row['gallery_id'];
    }
    $set='('.implode(','.$ids).')';

    //and finally get all images which associated in galleries in  $set
    "SELECT image_name FROM image WHERE image_galleryId in ".$set.";
    ....
    //and the rest... 
    also mysql manul says:
    If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants
    so these two queries surely outperfom 20 separate queries

    THANKS A LOT for the help and idea of thinking backwards...

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you're welcome

    thinking backwards is a skill, eh

    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
  •