issue with using a Subquery with left join-CodePudding

I am having an issue while being left joining 2 tables using subquery, getting an error :

Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 36
Incorrect syntax near 'PM'.

Here is the query I was trying to achieve:

SELECT 
    [SEASON],
    SUM(BL.QUANTITY) AS QUANTITY,
    PM.PRODUCT AS 'PRD_CLASS',
    PM.BRAND AS 'BRAND',
    PM.COST_PRICE AS 'COST',
    PM.DATECREATE   
FROM
    (SELECT
         QUANTITY, WAREHOUSE,
         CASE SUBSTRING(EXTENDED, 3, 1)
             WHEN '1' THEN 'SS'
             ELSE 'FW'
         END   SUBSTRING (EXTENDED, 1, 2) [SEASON]
     FROM 
         BINLABEL WITH (NOLOCK)
    ) BL
FROM 
    (SELECT
         PRODUCT AS 'PRDODUCT',
         BRAND AS 'BRAND',
         COST_PRICE AS 'COST',
         DATECREATE AS 'DATE_CREATE'    
     FROM 
         PRODTABLE  WITH (NOLOCK) 
    ) PM
LEFT JOIN 
    PRODTABLE AS PM ON BL.SKU = PM.SKU 
WHERE 
    WAREHOUSE = '0001'
GROUP BY 
    [SEASON]
ORDER BY 
    [SEASON]

CodePudding user response：

As I stated in the comments, you have 2 FROM clauses. Presumably the latter should be a CROSS JOIN or CROSS/OUTER APPLY (we don't know which). You also alias your second derived table as PM and then alias the table PRODTABLE as PM; that isn't allowed either as you can't have 2 objects with the same alias in the same scope. As I can't guess what alias you mean in other locations, I put {Alias} in places you need to add the relevant alias.

I also remove the NOLOCK table hints; you didn't reply as to why you "need" them and with respect I doubt you understand what it does considering the comments and question; more likely you have picked up someone elses terrible habit (get rid of said bad habit now).

I also, finally, get rid of the other bad habit of single quote aliases (see my comment on that too) and schema qualify (assuming dbo):

SELECT {Alias}.[SEASON],
       SUM(BL.QUANTITY) AS QUANTITY,
       {Alias}.PRODUCT AS PRD_CLASS,
       {Alias}.BRAND AS BRAND,
       {Alias}.COST_PRICE AS COST,
       {Alias}.DATECREATE
FROM (SELECT QUANTITY,
             WAREHOUSE,
             CASE SUBSTRING(EXTENDED, 3, 1)WHEN '1' THEN 'SS' ELSE 'FW' END   SUBSTRING(EXTENDED, 1, 2) AS [SEASON]
      FROM dbo.BINLABE) BL
     CROSS JOIN (SELECT PRODUCT AS PRDODUCT,
                        BRAND AS BRAND,
                        COST_PRICE AS COST,
                        DATECREATE AS DATE_CREATE
                 FROM dbo.PRODTABLE) PT
     LEFT JOIN dbo.PRODTABLE PM ON BL.SKU = PM.SKU --Think we can safely assume this is PM
WHERE {Alias}.WAREHOUSE = '0001'
GROUP BY {Alias}.[SEASON]
ORDER BY {Alias}.[SEASON];