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

Question

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 .

Answer 1

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

Answer 2

But I need one unique row for each Header record.

To me it is unclear if you mean one unique record per HT.O_ID or no duplicate records regarding all the four columns returned.

If it is the latter, add the DISTINCT keyword to your query (and add the schema names).

 SELECT DISTINCT HT.O_ID,
           HT.Type,
           HT.Total,
           IT.Image 

    FROM dbo.HEADER_TABLE HT 
    JOIN dbo.LINE_ITEM_TABLE LIT 
    ON LIT.O_ID = HT.O_ID 
    JOIN dbo.IMAGE_TABLE IT 
    ON IT.Item_ID = LIT.ITEM_ID 
    WHERE IT.SECTION = 'Retail';

Result

O_ID    Type    Total   Image
1001    Online  $10 URL

If that does not solve it

Then you would have to group by the values in the Header_Table And decide which Image you need to keep,do concatenation on it, ....

SELECT HT.O_ID,
       HT.Type,
       HT.Total,
       MAX(IT.Image) as MaxURL

FROM dbo.HEADER_TABLE HT 
JOIN dbo.LINE_ITEM_TABLE LIT 
ON LIT.O_ID = HT.O_ID 
JOIN dbo.IMAGE_TABLE IT 
ON IT.Item_ID = LIT.ITEM_ID 
WHERE IT.SECTION = 'Retail'
GROUP BY HT.O_ID,HT.Type,HT.Total;

---

Test data

CREATE TABLE dbo.HEADER_TABLE(O_ID int,Type varchar(100),Total varchar(10))
INSERT INTO dbo.HEADER_TABLE(O_ID,Type,Total)
VALUES  (1001,'Online','$10');

CREATE TABLE dbo.LINE_ITEM_TABLE(ID int,O_ID int,Item_ID varchar(4),Line_Total varchar(10));
INSERT INTO dbo.LINE_ITEM_TABLE(ID,O_ID,Item_ID,Line_Total)
VALUES  (1,1001,'P001','$2'),
        (2,1001,'P002','$2'),
        (3,1001,'P003','$2'),
        (4,1001,'P004','$2'),
        (5,1001,'P005','$2');


CREATE TABLE dbo.IMAGE_TABLE(Item_ID varchar(10),Image varchar(100),Section varchar(10))
INSERT INTO dbo.IMAGE_TABLE 
VALUES  ('P001','URL','Retail'),
        ('P002','URL','Retail'),
        ('P003','URL','Stock'),
        ('P004','URL','Retail'),
        ('P005','URL','Retail');