CODEWITHSUNDEEP
mysqlsql
David Lee
David Lee

Reputation: 3

MySQL syntax error, about union and as keywords

select distinct id, item from 
(
    (
        (
            select ID as id, "INNER" as item 
            from TREE 
            where ID in 
            (
                select P_ID from TREE as T1
            )  
            and P_ID is not null
        ) 
        as T3
    )
    union
    (
        (
            select ID as id, "ROOT" as item 
            from TREE 
            where P_ID IS NULL
        ) 
        as T2
    )
)

The TREE table has structure like this:

ID, P_ID
1,2
3,4
5,6

I am a noob in SQL and I really don't know why there is always problem when I am executing it.

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as T2 ) )' at line 22

Could you tell me? Thanks!

Upvotes: 0

Views: 72

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

Don't know what you are trying to achieve from query but fix the syntax error you can rewrite your query as

SELECT DISTINCT 
  id,
  item 
FROM(
  SELECT 
    ID AS id,
    "INNER" AS item 
  FROM
    TREE 
  WHERE ID IN (SELECT P_ID FROM TREE) 
  AND P_ID IS NOT NULL 

  UNION

  SELECT 
    ID AS id,
    "ROOT" AS item 
  FROM
    TREE 
  WHERE P_ID IS NULL
) as t

Upvotes: 3

Related Questions