How to add TOP 1 in query with left join in views?

Question

I have 3 same product in ID=42, with 3 different images. I want to take the first image from the product ID, I try adding "TOP 1", error

This is my query

CREATE OR REPLACE VIEW UserOrdersView
AS 
SELECT 
    u.[User_ID],
    p.Product_Name,
    p.Price, 
    o.Order_Price, 
    o.Order_ID, 
    i.[Image]
FROM Product p
LEFT JOIN Orders o ON o.Product_ID = p.Product_ID
INNER JOIN Users u ON u.[User_ID]= o.[User_ID]
LEFT JOIN Product_Images  i ON i.Product_ID = p.Product_ID
WHERE o.[User_ID] = 42

So you get an error? Why don't you tell us what the error is? And how did you use `TOP 1`? — HoneyBadger, Sep 27 '19 at 11:34
Please avoid images, add some sample data and expected output. [How to ask a question](https://stackoverflow.com/help/how-to-ask) — B3S, Sep 27 '19 at 11:39
Are you sure the question is about SQL Server? the syntax `Create or replace` is typical in Oracle, not in SQL Server — mnieto, Sep 27 '19 at 11:40

score 0 · Answer 1 · answered Sep 27 '19 at 12:20

You need to use OUTER APPLY to get top 1 image data from Product_image table based on Product ID.

Please check this Real life example, when to use OUTER / CROSS APPLY in SQL stackoverflow link for more knowledge.

Please check below updated view code for your answer.

CREATE OR REPLACE VIEW UserOrdersView
AS 
BEGIN

    SELECT 
        u.[User_ID],
        p.Product_Name,
        p.Price, 
        o.Order_Price, 
        o.Order_ID, 
        i.[Image]
    FROM Product p
    INNER JOIN Users u ON u.[User_ID]= o.[User_ID]
    LEFT JOIN Orders o ON o.Product_ID = p.Product_ID
    OUTER APPLY 
    (
        SELECT TOP 1            
            T2.[Image]
        FROM Product_Images T2
        WHERE T2.Product_ID = p.Product_ID
    ) i
    WHERE o.[User_ID] = 42

END
GO

Means you are getting any error or not giving you expected output. — JIKEN, Sep 27 '19 at 12:53

score 0 · Answer 2 · answered Sep 27 '19 at 12:41

WITH cte as (
    SELECT 
    u.[User_ID],
    p.Product_Name,
    p.Price, 
    o.Order_Price, 
    o.Order_ID, 
    i.[Image],
    ROW_NUMBER() OVER (PARTITION BY i.[Image] ORDER BY p.Product_Name) AS rn   
FROM Product p
LEFT JOIN Orders o ON o.Product_ID = p.Product_ID
INNER JOIN Users u ON u.[User_ID]= o.[User_ID]
LEFT JOIN Product_Images  i ON i.Product_ID = p.Product_ID
)
SELECT [User_ID],Product_Name,Price,Order_Price,Order_ID,[Image] FROM cte
WHERE rn=1

Put your all query inside a CTE with a new column that you will use to filter the results. This new column is produced with ROW_NUMBER() function partitioned by Product_Name

How to add TOP 1 in query with left join in views?

2 Answers2