June 2, 2003 at 9:32 am
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. 🙂
June 2, 2003 at 9:58 am
What do u mean by "make this into one record" ? Can u exalain in detail how u want ur output to be?
June 2, 2003 at 10:36 am
I think that kfenstad is looking to list out in a single record the 'large' and 'small' file names associated with a single product_id....
Does the following query resemble what is needed???
SET NOCOUNT ON
create table #Prod(Product_id Int)
create table #ProdImages(Product_id Int,ImageType Varchar(100),Image Varchar(100))
Insert #Prod(Product_id)Values (100)
Insert #ProdImages(Product_id,ImageType,Image) Values (100,'Large','1000.jpg')
Insert #ProdImages(Product_id,ImageType,Image) Values (100,'Small','1001.jpg')
Insert #Prod(Product_id)Values (101)
Insert #ProdImages(Product_id,ImageType,Image) Values (101,'Large','1002.jpg')
Insert #ProdImages(Product_id,ImageType,Image) Values (101,'Small','1003.jpg')
Insert #Prod(Product_id)Values (102)
Insert #ProdImages(Product_id,ImageType,Image) Values (102,'Large','1004.jpg')
--Insert #ProdImages(Product_id,ImageType,Image) Values (102,'Small','1005.jpg')
select Products.Product_id,ProdImages1.[Image] Large,
ProdImages2.[Image] Small from #Prod Products
LEFT OUTER JOIN #ProdImages ProdImages1 ON
(Products.Product_id = ProdImages1.Product_Id and ProdImages1.ImageType = 'Large')
LEFT OUTER JOIN #ProdImages ProdImages2 ON
(Products.Product_id = ProdImages2.Product_Id and ProdImages2.ImageType = 'Small')
drop table #Prod
drop table #ProdImages
SET NOCOUNT OFF
The LEFT OUTER JOIN is used in case you have
a product_id that has only a 'large' image and no 'small' image(or vice versa)...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply