Tutorial :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?


Solution:1

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


Solution:2

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.


Solution:3

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)   )  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »