0

I have the following problem on performing a left join between 2 tables.

Table SC1

COMPANY PEDIDO ITEM_NUMBER VALUE
1 4789 1 100
1 4789 2 120

and Table SD1

COMPANY PEDIDO ITEM_NUMBER DOC_NUMBER
1 7489 1 5874
1 7489 1 5875
1 7489 2 5880

I need to bring the Doc_Number to the SC1 table. But when I use left join and there are more than 1 Doc_Number fot the key, it duplicates the result.

Here's the result I'm getting:

COMPANY PEDIDO ITEM_NUMBER VALUE DOC_NUMBER
1 7489 1 100 5874
1 7489 1 100 5875
1 7489 2 120 5880

The result I want:

COMPANY PEDIDO ITEM_NUMBER VALUE DOC NUMBER
1 7489 1 100 5874-5875
1 7489 2 120 5880

Here's the code I'm using:

SELECT COMPANY,
       PEDIDO,
       ITEM_NUMBER,
       VALUE,
       DOC_NUMBER`
FROM SC1
     LEFT JOIN SD1 ON SC1.COMPANY = SD1.COMPANY
                  AND SC1.PEDIDO = SD1.PEDIDO
                  AND SC1.ITEM_NUMBER = SD1.ITEM_NUMBER;
GO
Thom A
  • 88,727
  • 11
  • 45
  • 75
Daniel Gatti
  • 1
  • You could group by `COMPANY, PEDIDO, ITEM_NUMBER, VALUE` and then take `CONCAT(MIN(DOC_NUMBER), '-', MAX(DOC_NUMBER))` but your expected results are unclear. Do you only want the first and last, or do you want all the `DOC_NUMBER` aggregated? – Charlieface Nov 01 '22 at 13:29
  • @Charlieface Yes, I want all the DOC_NUMBER related to that key, without duplicating the rows. I tried STRING_AGG but It didnt work with my sql version. – Daniel Gatti Nov 01 '22 at 14:00
  • Then you need to use a correlated subquery with `FOR XML`, as shown in those linked posts – Charlieface Nov 01 '22 at 14:01

0 Answers0