I have 3 tables as follows

CREATE TABLE tbl1 
(
    [ID] [INT]    NULL,
    [Name] [VARCHAR] (50)   NULL
) ;

CREATE TABLE tbl2 
(
    [ID] [INT]    NULL,
    [TranNo] [VARCHAR] (50)   NULL,
    [TranName] [VARCHAR] (50)   NULL  
) ;    

CREATE TABLE tbl3 
(
    [ID] [INT]    NULL,
    [ResultNo] [VARCHAR] (50)   NULL,
    [ResultName] [VARCHAR] (50)   NULL
) ;    

INSERT INTO tbl1 
VALUES (1,'Andy'), (2,'Lisa')

INSERT INTO tbl2 
VALUES (1, 'A1', 'Order'),
       (1, 'A2', 'Order'),
       (1, 'A3', 'Order'),
       (1, 'A4', 'Delivery'),
       (2, 'A5', 'Order'),
       (2, 'A6', 'Delivery'),
       (2, 'A7', 'Delivery')
 
INSERT INTO tbl3
VALUES (1, 'R1', 'Pending'),
       (1, 'R2', 'Success'),
       (2, 'R3', 'Success')

This is my query

Select 
    tbl1.*,
    tbl2.TranNo, tbl2.TranName, 
    tbl3.ResultNo, tbl3.ResultName
from 
    tbl1
left outer join
    tbl2 on tbl1.ID = tbl2.ID
left outer join
    tbl3 on tbl1.ID = tbl3.ID

Result: sqlfiddle

I want a no duplicate result / no extra result like this

or

Thanks for those of you who can help

CodePudding user response：

It looks like you want to zip the transaction (tbl2) and result (tbl3) rows together by ID and a generated row number. Those results can then joined back with tbl1.

Something like:

;WITH CTE_T AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY TranNo) AS RN
    FROM tbl2
),
CTE_R AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ResultNo) AS RN
    FROM tbl3
),
CTE_TR AS (
    SELECT
      COALESCE(T.ID, R.ID) AS ID,
      COALESCE(T.RN, R.RN) AS RN,
      T.TranNo, T.TranName, R.ResultNo, R.ResultName
    FROM CTE_T T
    FULL OUTER JOIN CTE_R R
        ON R.ID = T.ID
        AND R.RN = T.RN
)
SELECT A.ID, A.Name, TR.TranNo, TR.TranName, TR.ResultNo, TR.ResultName
FROM tbl1 A
LEFT JOIN CTE_TR TR
    ON TR.ID = A.ID
ORDER BY A.ID, TR.RN

Result:

See this db<>fiddle>.

Blanks can be inserted in place of nulls by wrapping some of the results with ISNULL(xxx, '').

Eliminating repeated values is best left to the presentation layer, but if you must, you could possibly use CASE expressions that check for RN = 1. Note that ID would need to be converted to a string if you wanted to include blank values in the same column.

See this db<>fiddle.