3

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 :

enter image description here

Line Table :

enter image description here

For each header record we can have multiple line items .

Images Table :

enter image description here

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 :

enter image description here

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 .

  • 4
    Hi and welcome to DBA.SE! The table create + insert statements could be useful to get an answer faster. (Testing purposes) – Randi Vertongen Mar 19 at 13:32
  • I need one unique row for each Header record. If so you MUST to build a criteria which will say what record from all record pack with the same header must be returned. – Akina Mar 19 at 13:32
  • Can you check and possibly update a couple of things in your question? Your SQL statement as currently written should return no rows; the second join needs to say on IT.ITEM_ID = LIT.ITEM_ID. Also, the sample data that you've included doesn't show any duplicates on the ITEM_ID field, so it doesn't look like the query (with the corrected join) would have any duplicates either. Are there actually multiple rows per Item ID in that table? – Hellion Mar 19 at 17:34
5

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

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.