asked on

i want to know is there any alternative for union in mysql while writing query

Hi Experts,

I want to know is there any alternative for union in mysql

i dont want to use union because this sql query i have to convert into hibernate hql query.
But hibernat hql doesn't support union.

can any one suggest alternative of union

Below is my query i want to use this query without union
query:
========
select * from category where parent_id =236282
UNION
select * from category where parent_id in(select category_id from
category where parent_id=236282)

Thanks,

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

F P

Yes. Please see my code example here:

http://codepad.co/s/ac9930

or take a look at this which is close to the same thing but requires a function(see my post to this answer here: https://www.experts-exchange.com/questions/28662047/How-can-i-get-all-childs-and-parent-ids-if-i-pass-the-value-in-a-query.html) ...

 SELECT  CONCAT(REPEAT('    ', level - 1), CAST(id AS CHAR)),
        parent,
        level
FROM    (
        SELECT  id, parent, IF(ancestry, @cl := @cl + 1, level + @cl) AS level
        FROM    (
                SELECT  TRUE AS ancestry, _id AS id, parent, level
                FROM    (
                        SELECT  @r AS _id,
                                (
                                SELECT  @r := parent
                                FROM    t_hierarchy
                                WHERE   id = _id
                                ) AS parent,
                                @l := @l + 1 AS level
                        FROM    (
                                SELECT  @r := 1218,
                                        @l := 0,
                                        @cl := 0
                                ) vars,
                                t_hierarchy h
                        WHERE   @r <> 0
                        ORDER BY
                                level DESC
                        ) qi
                UNION ALL
                SELECT  FALSE, hi.id, parent, level
                FROM    (
                        SELECT  hierarchy_connect_by_parent_eq_prior_id(id) AS id, @level AS level
                        FROM    (
                                SELECT  @start_with := 1218,
                                        @id := @start_with,
                                        @level := 0
                                ) vars, t_hierarchy
                        WHERE   @id IS NOT NULL
                        ) ho
                JOIN    t_hierarchy hi
                ON      hi.id = ho.id
                ) q
        ) q2

Open in new window

No function, but requires you to change the ID of the child element you want all the parents of:

SELECT  CONCAT(REPEAT('    ', level  - 1), id) AS treeitem, parent, level
FROM    (
        SELECT  _id AS id, parent,
                @cl := @cl + 1 AS level
        FROM    (
                SELECT  @r AS _id,
                        (
                        SELECT  @r := parent
                        FROM    t_hierarchy
                        WHERE   id = _id
                        ) AS parent,
                        @l := @l + 1 AS level
                FROM    (
                        SELECT  @r := 1218,
                                @l := 0,
                                @cl := 0
                        ) vars,
                        t_hierarchy h
                WHERE   @r <> 0
                ORDER BY
                        level DESC
                ) qi
        ) qo

Open in new window

srikotesh

ASKER

Excellent

F P

The first solution only accounts for one level of depth, the one i posted goes as deep as is there in one query. Something to keep in mind.

ste5an

hmm, the question was about only one level.. btw, as hibernate was mentioned: I would let hibernate resolve the hierarchy.

srikotesh

ASKER

select * from category where parent_id =236282
UNION
select * from master where parent_id =236282

in this scenario what i a have to do
OR WILL WORK ?

ste5an

How can I tell? What is the content of master? How does it relate to category?