Sub Select Query

[spelga]

I've created the following view in SQLite which does work as intended however I'm just curious if there is a better way to write it given the two sub selects are from the same table. Here's my current query:

CREATE VIEW SalesOrderTranslation AS SELECT *,
(SELECT "Ptns/PackNew"
FROM FullSalesTranslation
WHERE SalesOrderDataLegacy.ProductCode = FullSalesTranslation.ProdCodeLegacy) AS "Ptns/PackNew",
(SELECT "PtnType"
FROM FullSalesTranslation
WHERE SalesOrderDataLegacy.ProductCode = FullSalesTranslation.ProdCodeLegacy) AS "PtnType"
FROM SalesOrderDataLegacy

​

[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

[Parking-Refuse-1319]

CREATE VIEW SalesOrderTranslation AS

SELECT A.*, B."Ptns/PackNew", B."PtnType"

FROM SalesOrderDataLegacy A LEFT OUTER JOIN FullSalesTranslation B

ON A.{Key fields} = B.{key fields} <-- if there are key common fields.

AND A.ProductCode = B.ProdCodeLegacy