Ggtkuk

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