Join 2 separate tables (not with CTE)

February 8, 2022

I need to join 2 separate tables but not with CTEs but on a regular joins. I believe I probably may need to create a view (?)

Does someone know how it can be done? I usually do it with CTEs but here I need a join.

–JOIN TABLE 1 & 2 ON LEFT_OUTER POPO# AND POITM# TO R4PO# AND R4ITM#

-- Table 1
SELECT  RIGHT(wp.[POPO#], LEN(wp.[POPO#]) - 1) AS 'MATCH_PO',
wp.[POPO#],
wp.[POITM#]

FROM [Repit].[LEVYDTA].[WHSPOHM] wh
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSPOLM] wp
ON wh.[PHPO#]=wp.[POPO#]
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSVENM] wv
ON wh.[PHVEN#]=wv.[VNVEN#]
GROUP BY wp.[POPO#], wp.[POITM#]

-- Table2
SELECT [R4PO#], [R4ITM#], MAX([R4ADAT]) AS 'MAX R4ADAT', MAX([R4QTY]) AS 'MAX R4QTY', MAX([R4COD1]) AS 'MAX R4COD1'
FROM [REPIT].[LEVYDTA].[PUR855M]
GROUP BY [R4PO#], [R4ITM#]

>Solution :

Your code is all over the place.

Should be something like

SELECT key1, key2, MatchPo, MaxR4ADAT, MaxR4Qty, MaxR4Cod1
FROM (
       Select wp.[POPO#] as key1,
       wp.[POITM#] as key2,
       RIGHT(wp.[POPO#], LEN(wp.[POPO#]) - 1) as MatchPo
       FROM [Repit].[LEVYDTA].[WHSPOHM] wh
       LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSPOLM] wp
            ON wh.[PHPO#]=wp.[POPO#]
       LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSVENM] wv
            ON wh.[PHVEN#]=wv.[VNVEN#]
       GROUP BY wp.[POPO#], wp.[POITM#] ) as Data1
INNER JOIN 
      (SELECT [R4PO#] as key1, [R4ITM#] as key2, 
         MAX([R4ADAT]) AS MaxR4ADAT, 
         MAX([R4QTY]) AS MaxR4Qty, 
         MAX([R4COD1]) AS MaxR4Cod1
       FROM [REPIT].[LEVYDTA].[PUR855M]
       GROUP BY [R4PO#], [R4ITM#]
      ) as Data2 
    ON Data1.key1 = Data2.key1 AND Data1.key2 = Data2.key2