SQL Joins (not repeat information) -

i have 2 tables:

• t1: assemblylines (fields ordernumber, linecode, orderqty, sysuniqueid)

• t2: assemblylineissues (fields ordernumber, componentcode, issueqty, sysuniqueid, headersysuniqueid)

in example there is:

1 assemblyline record following information:

13088n03, 810044, 3, 12345678 

3 assemblylineissues records following information:

13088n03, 810044, 1, 11234567, 12345678 13088n03, 810044, 1, 12234567, 12345678 13088n03, 810044, 1, 13234567, 12345678 

my sql code is:

select  assemblylines.ordernumber, assemblylines.linecode, assemblylines.orderqty,  assemblylineissues.ordernumber, assemblylineissues.componentcode, assemblylineissues.issueqty  assemblylines left join assemblylineissues on assemblylines.sysuniqueid = assemblylineissues.headersysuniqueid 

the result is:

ordernumber linecode    orderqty    ordernumber2    componentcode   issueqty 13088c33    800038            3         13088c33    800038             1 13088c33    800038            3         13088c33    800038             1 13088c33    800038            3         13088c33    800038             1 

but looks 9 ordered, in fact 3 ordered. possible result looks like:

ordernumber linecode    orderqty    ordernumber2    componentcode   issueqty 13088c33    800038            3         13088c33    800038             1 13088c33    800038                      13088c33    800038             1 13088c33    800038                      13088c33    800038             1 

i can 2 separate sql's , use excel create it, avoid if possible.

i don't want use sum formula on issueqty either.

*thanks in advance. tried structure question in clear , concise manner, table have helped result. looks fine in editor, not in result, try how best show that.

you can desired result introducing case , row_number in query below.

select a.ordernumber,        a.linecode,        case            when row_number() over(partition a.ordernumber, a.linecode,b.ordernumber, b.componentcode, b.issueqty                                   order a.ordernumber) = 1 a.orderqty            else null        end order_quantity,        b.ordernumber,        b.componentcode,        b.issueqty assemblylines left join assemblylineissues b on a.sysuniqueid = b.headersysuniqueid; 

result:

ordernumber linecode    order_quantity  ordernumber  componentcode  issueqty ----------------------------------------------------------------------------- 13088n03    810044          3            13088n03       810044         1 13088n03    810044                       13088n03       810044         1 13088n03    810044                       13088n03       810044         1 

you can check demo here

hope help.