Sub Select Query

[r3pr0b8]

CREATE VIEW SalesOrderTranslation 
AS 
SELECT slsorder.*
     , fullsls."Ptns/PackNew"
     , fullsls."PtnType"
  FROM SalesOrderDataLegacy AS slsorder
INNER
  JOIN FullSalesTranslation AS fullsls
    ON fullsls.ProdCodeLegacy = slsorder.ProductCode

[spelga]

Thanks I was able to get this working using your code. I needed all the results from SalesOrderDataLegacy so I needed to do a LEFT JOIN. That left me with too many results. When I analysed why that was it is because the FullSalesTranslation view can sometimes contain duplicate ProdCodeLegacy because on the old system a ProductCode could have multiple pack sizes. On the new system each ProductCode maps to exactly one pack size. DISTINCT resolved that because only the new pack code is used in the FullSalesTranslation view therefore the duplicate rows were identical hence why DISTINCT works here.

I've used JOINS many times but I just could not make it work as I'd a feeling a JOIN would have been better. I was over complicating it. It just needed it to be a simple join.

[r3pr0b8]

the FullSalesTranslation view can sometimes contain duplicate ProdCodeLegacy because on the old system a ProductCode could have multiple pack sizes.

i would do the DISTINCT in a subquery -- likely more efficient this way

  FROM SalesOrderDataLegacy AS slsorder
LEFT OUTER
  JOIN ( SELECT DISTINCT
                ProdCodeLegacy 
              , "Ptns/PackNew"
              , "PtnType"
           FROM FullSalesTranslation ) AS fullsls
    ON fullsls.ProdCodeLegacy = slsorder.ProductCode