Get top 1 row value from third table while joining 3 tables mssql

I am new to mssql .here I need to get some data using joins between three tables .

Header join Lines join Images --> Result

Header Table :

Line Table :

For each header record we can have multiple line items .

Images Table :

Each Image will have unique Image or multiple images .Need to get 1 image url from the list of items for the header record.

Result Set :

Query :

SELECT HT.O_ID,
       HT.Type,
       HT.Total,
       IM.Image 

FROM HEADER_TABLE HT 

JOIN LINE_ITEM_TABLE LIT 
ON LIT.O_ID = HT.O_ID 

JOIN IMAGE_TABLE IT 
ON IT.IMAGE = LIT.ITEM_ID 

WHERE IT.SECTION = 'Retail'

This query returns multiple rows .But I need one unique row for each Header record.

Can anyone help me to fix .

You can use CROSS APPLY to SELECT just the TOP 1 image from each particular header. APPLY is similar to a function you can create "on the go" that links columns or expressions from outside to it's filters or joins.

SELECT
    -- Header columns:
    HT.O_ID,
    HT.Type,
    HT.Total,

    -- Columns from the CROSS APPLY result
    I.Image
FROM 
    HEADER_TABLE HT 
    CROSS APPLY (
        SELECT TOP 1 -- Just retrieve 1 row (for each HT row)
            IT.IMAGE
        FROM
            LINE_ITEM_TABLE LIT
            INNER JOIN IMAGE_TABLE IT ON IT.ITEM_ID = LIT.ITEM_ID
        WHERE
            LIT.O_ID = HT.O_ID AND  -- Link the outmost header "HT" record to it's lines "LIT"
            IT.SECTION = 'Retail') AS I

You can add an ORDER BY inside the CROSS APPLY to determine which image will get selected. You can also change the CROSS APPLY to OUTER APPLY if you want header rows to display even when there is no matching record coming from the APPLY operator (the IMAGE column will be NULL).

StackExchange Database Administrators Q#232523, answer score: 5