locked
Derived Table..incorrect syntax in SQL server Mgmt Studio... RRS feed

  • Question

  • Hi All,

    Thanks for your time, this might be a basic question but I get confused.. There is a syntax error near the FinalQuery...not sure why

    SELECT

    APPID,

    AppQuery.APPNAME AS AppFromAppQuery

    OwnerQuery.APPNAME AS APPFroMOwnerQuery,

    APPOWNER

    FROM

    (

            ( SELECT APPID,APPNAME FROM TABLE T1

            )AppQuery

    LEFT JOIN

             (SELECT APPID,APPNAME,APPOWNER FROM T2

             )OwnerQuery

             ON AppQuery.APPID=OwnerQuery.APPID

    )FinalQuery

    I get "Incorrect syntax" near FinalQuery  - I am trying to use the close bracket for the beginning select clause

    ...any help - thanks for your time

    Monday, August 3, 2020 4:43 PM

Answers

  • SELECT
    APPID,
    AppQuery.APPNAME AS AppFromAppQuery,
    OwnerQuery.APPNAME AS APPFroMOwnerQuery,
    APPOWNER
    
    FROM ( SELECT APPID,APPNAME FROM TABLE T1) AppQuery
    
    LEFT JOIN  (SELECT APPID,APPNAME,APPOWNER FROM T2 ) OwnerQuery
     ON AppQuery.APPID=OwnerQuery.APPID

    • Proposed as answer by Naomi N Monday, August 3, 2020 5:30 PM
    • Marked as answer by SQLServerRaj Monday, August 3, 2020 6:27 PM
    Monday, August 3, 2020 4:56 PM

All replies

  • SELECT
    APPID,
    AppQuery.APPNAME AS AppFromAppQuery,
    OwnerQuery.APPNAME AS APPFroMOwnerQuery,
    APPOWNER
    
    FROM ( SELECT APPID,APPNAME FROM TABLE T1) AppQuery
    
    LEFT JOIN  (SELECT APPID,APPNAME,APPOWNER FROM T2 ) OwnerQuery
     ON AppQuery.APPID=OwnerQuery.APPID

    • Proposed as answer by Naomi N Monday, August 3, 2020 5:30 PM
    • Marked as answer by SQLServerRaj Monday, August 3, 2020 6:27 PM
    Monday, August 3, 2020 4:56 PM
  • Thank you so much for your time. Does this mean I cannot name the outerquery with a name like FinalQuery

    SELECT

    FROM

    (

    )FinalQuery  -- Why this is not valid - could you please explain - I am lost here

    Monday, August 3, 2020 5:04 PM
  • Hi

    In your case AppQuery and Ownerquery are not applicable in select statement .Due to the whole alish as FinalQuery

    Thanks and regads

    Monday, August 3, 2020 5:14 PM
    • Proposed as answer by Naomi N Monday, August 3, 2020 5:30 PM
    Monday, August 3, 2020 5:24 PM
  • In your particular case I don't think you really even need subqueries at all. I think you can do a direct query from both t1 and t2, e.g.

    SELECT
    
    AppQuery.APPID,
    
    AppQuery.APPNAME AS AppFromAppQuery
    
    OwnerQuery.APPNAME AS APPFromOwnerQuery,
    
    OwnerQuery.APPOWNER
    
    FROM
    
    T1 as AppQuery
    
    LEFT JOIN T2 as OwnerQuery
    
             ON AppQuery.APPID=OwnerQuery.APPID


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Monday, August 3, 2020 5:34 PM
    Monday, August 3, 2020 5:33 PM
  • You can make that outer subquery and name it.  But it needs to be a SELECT query like your other subqueries.  And once you do that, then only the columns from the outer query are available to the final SELECT.  So it could look something like

    SELECT
    FinalQuery.APPID,
    FinalQuery.AppFromAppQuery,
    FinalQuery.APPFroMOwnerQuery,
    FinalQuery.APPOWNER
    FROM (SELECT AppQuery.APPID AS APPID, AppQuery.APPNAME AS AppFromAppQuery, OwnerQuery.APPNAME AS APPFroMOwnerQuery, APPOWNER
    FROM
      ( SELECT APPID,APPNAME FROM T1) AppQuery
    LEFT JOIN  (SELECT APPID,APPNAME,APPOWNER FROM T2 ) OwnerQuery
      ON AppQuery.APPID=OwnerQuery.APPID) FinalQuery
    Tom

    Monday, August 3, 2020 5:34 PM