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 :)If this in Oracle, you could use inline views as follows :

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 :)|||i am working in SQL server.|||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.