What is wrong with this query (Select as RES1 Union Select where param IN (RES1)

Question

The query:

SELECT ct_cid as level1 FROM cat_tree WHERE ct_sid=$sid_int AND ct_parent =$cid_int) UNION (SELECT ct_cid as level2 FROM cat_tree WHERE ct_sid=$sid_int AND ct_parent IN level1)

The query is supposed to find the child nodes up to 2 levels of a tree node. I get "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 'level1)" How can I use the results of the 1st select in the 2nd select?

Answer 1

This is not how SQL works. IN requires a list of values (or a subselect which creates the list).

Please post the table structure of the tables you are querying, along with explanations what the fields mean. Then maybe we can help.

Answer 2

This is untested, but the structure you're after might be :

SELECT ct_cid as level2 FROM cat_tree WHERE ct_sid=$sid_int AND ct_parent IN 
(
   SELECT ct_cid as level1 FROM cat_tree WHERE ct_sid=$sid_int AND ct_parent =$cid_int) 
)

Answer 3

you don't need parens around the second SELECT statement... Also, the field names and order must match in BOTH SQL-Select statements. What you may want is something like

select 1 as LevelID, CT_CID from cat_tree where...  
UNION
select 2 as levelID, CT_CID from cat_tree where ...

Now, your other half of the question... using the results of the first... Redo the query as a sub-select in the SECOND query...

select 1 as LevelID, CT_CID 
    from cat_tree
    where ct_sid = $sid_int
      and ct_parent = $cid_int
union
select 2 as LevelID, CT_CID
    from cat_tree
    where ct_sid = $sid_int
      and ct_parent IN
              ( select ct_cid  
                     from cat_tree
                     where ct_sid = $sid_int
                       and ct_parent in $cid_int )

Notice the parens are only needed in the SUB-SELECT, not the UNION select. Not knowing your data, there might be an easier approach to what you are trying to get