[kfenstad]

hello,

i have a view:

SELECT
products.product_id,
productImageTypes.ImageType,
productImages.Image
FROM
products INNER JOIN
productImages ON products.product_id = productImages.product_id INNER JOIN
productBrands ON products.productBrand_id = productBrands.productBrand_id INNER JOIN
productImageTypes ON productImages.productImageType_id = productImageTypes.productImageType_id
WHERE (productBrands.productBrand_value = 'ThisBrand')

which returns:
product_id ImageType Image
113042 Large 26227.jpg
113042 Small 26228.jpg

i now need to make this into one record. i am trying to use a case statement like:

SELECT
products.product_id,
CASE
WHEN productImageTypes.ImageType = 'Large'
THEN productImages.Image
ELSE
NULL
END as largeImage
CASE
WHEN productImageTypes.ImageType = 'Small'
THEN productImages.Image
ELSE
NULL
END as smallImage
FROM
products INNER JOIN
productImages ON products.product_id = productImages.product_id INNER JOIN
productBrands ON products.productBrand_id = productBrands.productBrand_id INNER JOIN
productImageTypes ON productImages.productImageType_id = productImageTypes.productImageType_id
WHERE (productBrands.productBrand_value = 'ThisBrand')


but I can only get this type of output:
product_id largeImage smallImage
113042 26227.jpg NULL
113042 NULL 26228.jpg

how can i change my query so i get what i need? thanks for any help.

[andrewst]

What is the output that you want to see?

[kfenstad]

product_id largeImage smallImage
113042 26227.jpg 26228.jpg

[andrewst]

Originally posted by kfenstad
product_id largeImage smallImage
113042 26227.jpg 26228.jpg

Try this:

SELECT
products.product_id,
MAX(CASE WHEN productImageTypes.ImageType = 'Large' THEN productImages.Image END) largeImage,
MAX(CASE WHEN productImageTypes.ImageType = 'Small' THEN productImages.Image END) smallImage,
FROM
products INNER JOIN
productImages ON products.product_id = productImages.product_id INNER JOIN
productBrands ON products.productBrand_id = productBrands.productBrand_id INNER JOIN
productImageTypes ON productImages.productImageType_id = productImageTypes.productImageType_id
WHERE (productBrands.productBrand_value = 'ThisBrand')
GROUP BY products.product_id
ORDER BY products.product_id;

[kfenstad]

thank you, it works great.

[kfenstad]

maybe you could help me with something else too. in the productImages table, there is a field called primary. there are multiple images for each product_id in the table, but each product_id should only have one primary image. right now all of the images are set to 0 (nonprimary) but i need to set one image for each product_id to 1 (primary). it doesn't matter which, just the top 1 image for that product_id. is this possible with an update statement, and if so, do you have any ideas for me?

thanks again for all your help.

[andrewst]

Originally posted by kfenstad
maybe you could help me with something else too. in the productImages table, there is a field called primary. there are multiple images for each product_id in the table, but each product_id should only have one primary image. right now all of the images are set to 0 (nonprimary) but i need to set one image for each product_id to 1 (primary). it doesn't matter which, just the top 1 image for that product_id. is this possible with an update statement, and if so, do you have any ideas for me?

thanks again for all your help.

If it doesn't matter which one:

UPDATE productImages
SET primary=1
WHERE product_id, image IN
( SELECT product_id, MAX(image)
FROM productImages
GROUP BY product_id
);

That will pick the image with the "max" image name.

[kfenstad]

Originally posted by andrewst
If it doesn't matter which one:

UPDATE productImages
SET primary=1
WHERE product_id, image IN
( SELECT product_id, MAX(image)
FROM productImages
GROUP BY product_id
);

That will pick the image with the "max" image name.

i am getting an error, Incorrect syntax near ','.

what am i doing wrong?

thanks.

[andrewst]

Originally posted by kfenstad
i am getting an error, Incorrect syntax near ','.

what am i doing wrong?

thanks.

Sorry, that should have read:

UPDATE productImages
SET primary=1
WHERE (product_id, image) IN
( SELECT product_id, MAX(image)
FROM productImages
GROUP BY product_id
);