SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting gallery, img and number of comments

    I have 3 tables, Gallery, Image and img_comments

    one gallery has many images, one image has many comments.

    I can get gallery info, img info and number of comments like this:

    Code:
    select gallery.*,image.*,
    count(img_comments.image_ID) 
    from gallery, image, img_comments
    where gallery.gallery_ID = image.gallery_ID
    and gallery.gallery_ID = 60
    and image.image_ID = img_comments.image_ID
    group by image.image_ID;
    but the problem is that this only returns a recordset with images which have comments, when i also want to get the images which dont have comments. how do i do this?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select gallery.gallery_ID
         , gallery.foo
         , image.image_ID
         , image.bar
         , count(img_comments.image_ID) 
      from gallery
    left outer
      join image
        on gallery.gallery_ID = image.gallery_ID
    left outer
      join img_comments
        on image.image_ID = img_comments.image_ID
     where gallery.gallery_ID = 60
    group 
        by gallery.gallery_ID
         , gallery.foo
         , image.image_ID
         , image.bar
    you cannot use "select star" in this query

    you have to list the columns that you want, in both the SELECT list and the GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Thanks

    I just found out how to go about running these kind of queries. For anyone who is interested there is a very good SQL tutorial here: http://www.w3schools.com/sql/

    and in OUTER JOINS a nutshell:
    The LEFT JOIN returns all the rows from the first table, even if there are no matches in the second table . If there are rows in first table that do not have matches in second table, those rows also will be listed.
    Code:
    eg:
    SELECT field1, field2, field3
    FROM first_table
    LEFT JOIN second_table
    ON first_table.keyfield = second_table.foreign_keyfield
    The RIGHT JOIN returns all the rows from the second table, even if there are no matches in the first table. If there had been any rows in the second table that did not have matches in the first table, those rows also would have been listed.
    Code:
    SELECT field1, field2, field3
    FROM first_table
    RIGHT JOIN second_table
    ON first_table.keyfield = second_table.foreign_keyfield

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    poor examples

    SELECT field1, field2, field3
    FROM first_table
    LEFT JOIN second_table
    ON first_table.keyfield = second_table.foreign_keyfield

    which tables do field1, field2, field3 come from?

    what happens when you use
    ... count(*)
    instead of
    ... count(second_table.foreign_keyfield)
    in a left outer join with a GROUP BY?
    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
  •