Dtjytyk

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

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');