Selecting Individual Rows From Joined Table

elskan · #1 (**permalink**) 04-17-07, 06:50

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

r937 · #2 (**permalink**) 04-17-07, 07:03

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 )

gvee · #3 (**permalink**) 04-17-07, 07:04

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

r937 · #4 (**permalink**) 04-17-07, 07:27

um, no, george -- and why do you have DISTINCT?

elskan · #5 (**permalink**) 04-17-07, 07:53

thanks R937 your solution worked perfectly, and george thanks for your input too.

gvee · #6 (**permalink**) 04-17-07, 09:01

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?

r937 · #7 (**permalink**) 04-17-07, 09:04

and your sql restricts the results to one image per product-id how, exactly?

gvee · #8 (**permalink**) 04-17-07, 09:07

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

gvee · #9 (**permalink**) 04-17-07, 09:12

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

r937 · #10 (**permalink**) 04-17-07, 09:17

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?

gvee · #11 (**permalink**) 04-17-07, 09:21

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?

r937 · #12 (**permalink**) 04-17-07, 09:23

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

gvee · #13 (**permalink**) 04-17-07, 09:28

*nods in appreciation*
Clever, will bear that in mind

r937 · #14 (**permalink**) 04-17-07, 09:31

george, maybe you missed the smiley

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

gvee · #15 (**permalink**) 04-17-07, 09:36

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech