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

    Unanswered: can i make one query out of these two?

    i have two views. the first finds related images for products, but only if the image is the primary image. here it is:

    CREATE VIEW images AS
    SELECT
    p.product_id,
    pI.productImage_title as productImage_title
    FROM
    dbo.products p INNER JOIN
    dbo.productImages pI ON p.product_id = pI.product_id
    WHERE pI.productImage_primary = 1

    the second view gets all the products, and uses the first view to find an image, if there is one:

    SELECT
    p.product_id as product_id,
    p.product_partNumber as part_number,
    p.product_name as product_name,
    i.productImage_title as small_image,
    FROM
    dbo.products p LEFT OUTER JOIN
    dbo.images i ON p.product_id = i.product_id

    i would like to be able to do this with just one view. i'm not sure how to do it though while still limiting the images to only primary ones.

    thanks for any help

  2. #2
    Join Date
    May 2003
    Posts
    87

    Re: can i make one query out of these two?

    If this in Oracle, you could use inline views as follows :

    Code:
    SELECT     
      p.product_id as product_id,
      p.product_partNumber as part_number,
      p.product_name as product_name,
      i.productImage_title as small_image,
    FROM
      dbo.products p,
      (SELECT p.product_id,
                   pI.productImage_title as productImage_title
        FROM dbo.products p INNER JOIN
                 dbo.productImages pI ON p.product_id = pI.product_id
        WHERE pI.productImage_primary = 1) i
    WHERE p.product_id = i.product_id(+)
    Originally posted by kfenstad
    i have two views. the first finds related images for products, but only if the image is the primary image. here it is:

    CREATE VIEW images AS
    SELECT
    p.product_id,
    pI.productImage_title as productImage_title
    FROM
    dbo.products p INNER JOIN
    dbo.productImages pI ON p.product_id = pI.product_id
    WHERE pI.productImage_primary = 1

    the second view gets all the products, and uses the first view to find an image, if there is one:

    SELECT
    p.product_id as product_id,
    p.product_partNumber as part_number,
    p.product_name as product_name,
    i.productImage_title as small_image,
    FROM
    dbo.products p LEFT OUTER JOIN
    dbo.images i ON p.product_id = i.product_id

    i would like to be able to do this with just one view. i'm not sure how to do it though while still limiting the images to only primary ones.

    thanks for any help

  3. #3
    Join Date
    May 2003
    Posts
    25
    i am working in SQL server.

  4. #4
    Join Date
    May 2003
    Posts
    87
    Sorry, I cant help you with exact syntax but SQL Server also supports inline views -- dont know which version. May be, you can look into the documentation.

    Originally posted by kfenstad
    i am working in SQL server.

Posting Permissions

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