Results 1 to 9 of 9
  1. #1
    Join Date
    May 2003
    Posts
    25

    Unanswered: select statement w/case statement

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: select statement w/case statement

    What is the output that you want to see?

  3. #3
    Join Date
    May 2003
    Posts
    25
    product_id largeImage smallImage
    113042 26227.jpg 26228.jpg

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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;

  5. #5
    Join Date
    May 2003
    Posts
    25

    Smile

    thank you, it works great.

  6. #6
    Join Date
    May 2003
    Posts
    25

    another question.

    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.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: another question.

    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.

  8. #8
    Join Date
    May 2003
    Posts
    25

    Re: another question.

    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.

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: another question.

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

Posting Permissions

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