Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    Join Date
    Apr 2005
    Posts
    30

    Unanswered: Selecting Individual Rows From Joined Table

    Hi

    I am trying to select one individual record from multiple tables.

    I have a products, product_images, category, sub-category and main table.

    The product table holds product name, the product_images table holds images relating to the product id, the category and sub-category tables hold the category info and the main table holds them all together.

    I wish to select just one image from the images table along with the one product.

    What I have so far is

    SELECT DISTINCT products.product_name, product_images.thumbnail, product_images.main_image, product_options.price, products.product_id, category.cat_name, sub_category.sub_category_name
    FROM product_images, main INNER JOIN product_options ON main.product_options_id = product_options.product_options_id INNER JOIN products ON main.product_id = products.product_id INNER JOIN category ON main.category_id = category.category_id INNER JOIN sub_category ON main.sub_category_id = sub_category.sub_category_id;

    The trouble is it is displaying all of the images for each product-id,how do I get it to just display the one image per product-id?

    Thanks in advance for any help

    Cheers

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT P.product_name
         , PI.thumbnail
         , PI.main_image
         , PO.price
         , P.product_id
         , C.cat_name
         , SC.sub_category_name
      FROM main as M
    INNER 
      JOIN product_options as PO
        ON PO.product_options_id = M.product_options_id
    INNER 
      JOIN products as P
        ON P.product_id = M.product_id 
    INNER 
      JOIN category as C
        ON C.category_id = M.category_id
    INNER 
      JOIN sub_category as SC
        ON SC.sub_category_id = M.sub_category_id
    INNER
      JOIN product_images as PI
        ON PI.product_id = M.product_id
       AND PI.thumbnail =
           ( select max(thumbnail)
               from product_images
              where product_id = M.product_id )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I imagine you want something like:
    Code:
    SELECT DISTINCT
    	products.product_id,
    	products.product_name,
    	product_images.thumbnail,
    	product_images.main_image
    FROM	products
    INNER JOIN product_images
    	ON products.product_id = product_images.product_id
    
    --Using aliases:
    SELECT DISTINCT
    	p.product_id,
    	p.product_name,
    	p_i.thumbnail,
    	p_i.main_image
    FROM	products p
    INNER JOIN product_images p_i
    	ON p.product_id = p_i.product_id
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    um, no, george -- and why do you have DISTINCT?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2005
    Posts
    30
    thanks R937 your solution worked perfectly, and george thanks for your input too.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    um, no, george -- and why do you have DISTINCT?
    You're completely right, distinct is not needed. However, I went for the answer to this quesion:
    Quote Originally Posted by elskan
    how do I get it to just display the one image per product-id?
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and your sql restricts the results to one image per product-id how, exactly?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Re-read the first post - I was assuming that product_id was a unique identifier and that there was only one image per product (which is not true!)

    So I'm going to go away now and (try) re-write this properly

    Thank you for pointing that our Rudy
    George
    Home | Blog

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To Rudy,
    Code:
    SELECT	p.product_id,
    	p.product_name,
    	p_i.thumbnail,
    	p_i.main_image
    FROM	products p
    INNER JOIN product_images p_i
    	ON p.product_id = p_i.product_id
    WHERE p_i.thumbnail IN
    	(
    	SELECT	MAX(thumbnail)
    	FROM	product_images
    	WHERE	product_images.product_id = p.product_id
    	)
    Love george
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that looks a lot like what i posted in post #2 except that it is missing the other tables involved

    i'm curious as to why you used IN

    how many MAXes do you expect the subquery to return?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well I didn't use your query, I wanted to get it right on me own
    good point at the IN... it'd work, but an equals is really what's needed!

    Wasn't bothering with the joins because the ones he had seemed reasonable!

    So swap the IN for an equals and you'd be happy?
    George
    Home | Blog

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yup, i always use = when the subquery uses MAX or MIN

    that way, in case it does ever return more than one value, i'll get a runtime syntax error ("subquery cannot return more than one row") and thus get an early warning that something else is wrong

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *nods in appreciation*
    Clever, will bear that in mind
    George
    Home | Blog

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    george, maybe you missed the smiley

    it will never happen that a MAX or MIN subquery will return more than one row

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Question:
    Say I have a date field and I use the same structured query using MAX(MyDate) and my table has more than one record with the same MyDate - what will it do? Will it pick up the record that is "physically" maximum?

    That's what popped into my head after post #12!
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •