1

I got a problem which is very specific to the database I got in work. I can't really change something and it is really huge so I need to deal with it.

What I want to do is to link a product that came back to the factory. The fact is that the product can be back more than one time.

In database, where the product leaves the factory, we don't use anymore his ID. When he comes back, we create a new ID and use a foreign key named "idProductParent".

So it is not really hard to link the product to his ID when he was in the factory.

The problem is that if the product leaves another time the factory AND come back weeks later (we are doing After-Sales Services), we don't have the "idProductParent" used but the "idProductPrevious" which is a foreign key to the last time he came in the factory.

What I want to make is to JOIN the product in the factory with his last idProduct of After-Sales Services.

If I make a JOIN on the "idProductPrevious" when not null or "idProductParent" when not null I'll have some result but products that came back more than one time will not be shown.

If you have some idea or if you need some details just say it.

Thanks.

SELECT p.idProduct as ProductFactory, pback.idProduct ProductBack
FROM PRODUCT p 
JOIN PRODUCT pback ON pback.idProductParent = p.idProduct

This request gives me the product id from factory and the product id where the product is back one time.

If the product come back a new time, I'll need to use this query

SELECT p.idProduct as ProductFactory, pback.idProduct ProductBack, pback2.idProduct ProductBack2
FROM PRODUCT p 
LEFT JOIN PRODUCT pback ON pback.idProductParent = p.idProduct
LEFT JOIN PRODUCT pback2 ON pback2.idProductPrevious = pback.idProduct

OUTPUT wanted :

ProductFactory        ProductBack              ProductBack2
 1                         50                       51

If the product come back another time, I'll use this :

SELECT p.idProduct as ProductFactory, pback.idProduct ProductBack, pback2.idProduct ProductBack2, pback3.idProduct ProductBack3
FROM PRODUCT p 
LEFT JOIN PRODUCT pback ON pback.idProductParent = p.idProduct
LEFT JOIN PRODUCT pback2 ON pback2.idProductPrevious = pback.idProduct
LEFT JOIN PRODUCT pback3 ON pback3.idProductPrevious = pback2.idProductPrevious

OUTPUT wanted :

ProductFactory        ProductBack              ProductBack2               ProductBack3
 1                         50                       51                         52

I'm not sure about the "LEFT" but I think you got what is my problem. I don't know how many times the product will be back and I don't want to use as many left join as the product might be back.

yeahgd
  • 58
  • 5
  • 1
    A SQL query returns a *fixed* set of columns. You appear to be asking for a variable number of columns. The only approach is dynamic SQL. – Gordon Linoff Aug 06 '20 at 11:17
  • Even if I only want a column with the LAST ProductBack (that can be a product back more than one time) I need to use dynamic SQL? – yeahgd Aug 06 '20 at 11:24
  • . . That would be a *different* question. You should ask a *new* question, with appropriate sample data and explanation -- if the hint "recursive CTE" doesn't provide the guidance you need. – Gordon Linoff Aug 06 '20 at 12:42

0 Answers0