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,
LVL 2
srikoteshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
E.g.

SELECT	* 
FROM	category 
WHERE	parent_id = 236282
	OR parent_id IN (
			SELECT	category_id 
			FROM	category 
			WHERE 	parent_id = 236282
		);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
F PCommented:
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: http://www.experts-exchange.com/Database/MySQL/Q_28662047.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

0
srikoteshAuthor Commented:
Excellent
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

F PCommented:
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.
0
ste5anSenior DeveloperCommented:
hmm, the question was about only one level.. btw, as hibernate was mentioned: I would let hibernate resolve the hierarchy.
0
srikoteshAuthor Commented:
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 ?
0
ste5anSenior DeveloperCommented:
How can I tell? What is the content of master? How does it relate to category?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.