CODEWITHSUNDEEP
sql-server
F.Kahalaj
F.Kahalaj

Reputation: 86

duplicate column when join a table twice

when i join a table two time for get different data from table two, if table two get different row count data some column be duplicate.

select 
o.order_no
,sr.reason as reason1
,datediff(minute,s.begin_stop,s.end_stop) as s1
,sr2.reason as reason2
,datediff(MINUTE,s2.begin_stop,s2.end_stop) as s2
from [order] o 
left join [stops] s on s.order_id=o.id and s.section='print'
join [stopreason] sr on s.error_code=sr.code
left join [stops] s2 on s2.order_id=o.id and s2.section='laminate1'
join [stopreason] sr2 on sr2.code=s2.error_code
where datepart(ww,o.date_onexit)=39
order by order_no

and i get this resoult

enter image description here

Upvotes: 1

Views: 60

Answers (1)

gofr1
gofr1

Reputation: 15987

Try this one:

;WITH part1 AS (
    select  o.order_no,
            sr.reason as reason1,
            datediff(minute,s.begin_stop,s.end_stop) as s1,
            ROW_NUMBER() OVER (PARTITION BY o.order_no ORDER BY o.order_no) rn
    from [order] o 
    left join [stops] s 
        on s.order_id=o.id and s.section='print'
    join [stopreason] sr 
        on s.error_code=sr.code
    where datepart(ww,o.date_onexit)=39
), part2 AS (
    select  o.order_no,
            sr2.reason as reason2,
            datediff(MINUTE,s2.begin_stop,s2.end_stop) as s2,
            ROW_NUMBER() OVER (PARTITION BY o.order_no ORDER BY o.order_no) as rn
    from [order] o 
    left join [stops] s2 
        on s2.order_id=o.id and s2.section='laminate1'
    join [stopreason] sr2 
        on sr2.code=s2.error_code
    where datepart(ww,o.date_onexit)=39
)

SELECT  p1.order_no,
        p1.reason1,
        p1.s1,
        p2.reason2,
        p2.s2
FROM part1 p1
LEFT JOIN part2 p2 
    ON p1.order_no = p2.order_no AND p1.rn = p2.RN
order by p1.order_no

First CTE (part1) collects data for section='print', second - for section='laminate1'. And we add ROW_NUMBER(). Then join two CTEs.

One more way:

;WITH part1 AS (
    select  o.order_no,
            sr.reason as reason,
            datediff(minute,s.begin_stop,s.end_stop) as s,
            s.section,
            ROW_NUMBER() OVER (PARTITION BY o.order_no,s.section ORDER BY o.order_no) rn
    from [order] o 
    left join [stops] s 
        on s.order_id=o.id and (s.section='print' or s.section='laminate1' )
    join [stopreason] sr 
        on s.error_code=sr.code
    where datepart(ww,o.date_onexit)=39
)

SELECT  p1.order_no,
        p1.reason1,
        p1.s1,
        p2.reason2,
        p2.s2
FROM part1 p1
LEFT JOIN part1 p2
    ON p1.order_no = p2.order_no AND p1.rn = p2.RN AND p2.section='laminate1'
WHERE p1.section='print'
order by p1.order_no

Upvotes: 1

Related Questions