select statement w/case statement

• kfenstad

  SSC Journeyman

  Points: 86

  More actions

  June 2, 2003 at 9:32 am

  #80994

  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. 🙂

• Bavineni

  SSC Enthusiast

  Points: 147

  More actions

  June 2, 2003 at 9:58 am

  #459364

  What do u mean by "make this into one record" ? Can u exalain in detail how u want ur output to be?

• winash

  SSChampion

  Points: 11446

  More actions

  June 2, 2003 at 10:36 am

  #459374

  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)...