How can i get all childs and parent ids if i pass the value in a query

Hi experts,

I HAVE A TABLE MASTER
THIS TABLE HAVING COLUMN CALLED PARENT_ID
THIS PARENT_ID MIGHT HAVE ANOTHER PARENT_ID or some times it may not

i want to get all ids in a single query if i have pass the value

Example:

select Parent_id from master where ci_id=5789;
o/p:
5788
select Parent_id from master where ci_id=5788;
o/p:
5785
select Parent_id from master where ci_id=5785;
null

with single query i am expecting the out put like
if i pass the 5785 value
i have to get 5789,5788

can any one suggest me how to get the values.
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.

slightwv (䄆 Netminder) Commented:
What you are after is a Hierarchical Query:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries003.htm#SQLRF52315

Try this:
select ci_id from master
where ci_id != 5785
start with ci_id=5785
connect by prior ci_id=parent_id
/

Open in new window

0
srikoteshAuthor Commented:
I am getting syntax exception
ci_id=parent_id
Note:
=====
the above condition will not be equal in my scenario

if suppose ciid=5789 its parent id is 5788
and           ciid=5788 its parent id is 5785.
0
slightwv (䄆 Netminder) Commented:
>>I am getting syntax exception

I tested what I posted using sqlplus against 11.2.0.2.

What tool are you using and what database version (all 4 numbers please)?

>>the above condition will not be equal in my scenario

What I posted produces the results you wanted given the data you provided.

I don't understand what you are trying to tell me.

Please add to the test case below and explain where it breaks.  It is what I used to test what I posted.

/*
drop table tab1 purge;
create table tab1(ci_id number, parent_id number);

insert into tab1 values(5789,5788);
insert into tab1 values(5788,5785);
insert into tab1 values(5785,null);
commit;
*/


select ci_id from tab1
where ci_id != 5785
start with ci_id=5785
connect by prior ci_id=parent_id
/

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

srikoteshAuthor Commented:
i am using mysql command prompt

i have executed the above query but i am getting syntax exception.ERROR 1064 (42000):

now i have tried with the below query i am getting partial output.

select cm.ci_id,cm.CI_Parent_CI from CI_master as cm left join CI_master as cm2 on cm2.CI_Parent_CI = cm.ci_id where cm.ci_id =5789
o/p:5788

i have to add subquery to get the rest of the result.
0
slightwv (䄆 Netminder) Commented:
Sorry.  What I posted was for Oracle.  I didn't see the MySQL zone.

I cannot help with MySQL.
0
F PCommented:
.... from me. http://codepad.co/s/ac9930

-- Add the following function to your MySQL database and replace the fields as needed to match your structure (hint: everything that starts with _ or @ don't touch):
DROP FUNCTION IF EXISTS category_connect_by_parent_eq_prior_id;
DELIMITER $$
CREATE FUNCTION category_connect_by_parent_eq_prior_id(value INT)
  RETURNS int(11)
  BEGIN
    DECLARE _id INT;
    DECLARE _parent INT;
    DECLARE _next INT;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

    SET _parent = @id;
    SET _id = -1;

    IF @id IS NULL
    THEN
        RETURN NULL;
    END IF;

    LOOP
        SELECT MIN(category_id)
        INTO @id
        FROM categories_table
        WHERE parent_id = _parent
                    AND category_id > _id;
        IF @id IS NOT NULL OR _parent = @start_with
        THEN
            SET @level = @level + 1;
            RETURN @id;
        END IF;
        SET @level := @level - 1;
        SELECT
            category_id,
            parent_id
        INTO _id, _parent
        FROM categories_table
        WHERE category_id = _parent;
    END LOOP;
    END
$$
DELIMITER ;

Open in new window


-- Now use this query (again replace where needed the important parts) to give a structured hierarchal list of a category, all its sub-categories, and children therein recursively:
    SELECT
        `hi`.`category_id` AS `category_id`,
        `parent_id`,
        `level`
    FROM (	SELECT
                category_connect_by_parent_eq_prior_id(category_id) AS `id`,
                @level AS `level`
            FROM
                ( SELECT
                    @start_with := 0,
                    @id := @start_with,
                    @level := 0
                ) AS vars, `categories_table`
            WHERE
                @id IS NOT NULL
        ) AS `ho`
    JOIN
        `categories_table` AS `hi` ON `hi`.`category_id` = `ho`.`id`;

Open in new window

-- Here's and example of what you'll see:
category_id		parent_id		level
1			0			1
	2		1			2
		7 	2			3

Open in new window

0
F PCommented:
Change 1218 to the last child... If you only want a specific chain of related.

http://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/

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


or...


SELECT  @r AS _id,
         (
         SELECT  @r := parent
         FROM    t_hierarchy
         WHERE   id = _id
         ) AS parent,
         @l := @l + 1 AS lvl 
 FROM    (
         SELECT  @r := 1218,
                 @l := 0,
                 @cl := 0
         ) vars,
         t_hierarchy h
WHERE    @r <> 0

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
srikoteshAuthor Commented:
Thanks for ur suggestion.
0
F PCommented:
Very welcome. I personally recommend my first answer for most people, but only because you're going to have to pull a nav bar in on most pages with that array info anyway. It'll save time to use what's in memory than ask mysql., so, maybe a class variable? .
0
srikoteshAuthor Commented:
hi can u suggest me how can i combine the below query
i want to get all 6 row with single query

mysql> select * from category where parent_id=236282;
+-------------+-----------+
| category_id | parent_id |
+-------------+-----------+
|      236395 |    236282 |
|      236398 |    236282 |
|      246466 |    236282 |
+-------------+-----------+
3 rows in set (0.00 sec)


mysql> select * from category where parent_id in(236395,236399,246466);
+-------------+-----------+
| category_id | parent_id |
+-------------+-----------+
|      236300 |    236395 |
|      236305 |    236395 |
|      236309 |    246466 |
+-------------+-----------+
3 rows in set (0.00 sec)
0
F PCommented:
I think you're asking how to do an OR operator, or just add the first parent_id into the IN filter within your WHERE like:

SELECT
    *
FROM
    category
WHERE
    parent_id = 236282
    OR parent_id IN (236395, 236399, 246466);

Open in new window


or the other way I mentioned:

SELECT
    *
FROM
    category
WHERE
    parent_id IN (236282, 236395, 236399, 246466);

Open in new window


I don't see how else you are asking to do that query, unless you mean a UNION or JOIN?
0
srikoteshAuthor Commented:
Hi,

input value will come dynamically,i dont know which value will come as input.

what i have to do is ?
1.take the input identify what are categories id belongs to this parent id.(here i will get one output with this query)

2.Again for this output what are categories is belong to these parent ids(1st query output).

Example:
my first input is 236282

1.i have executed this query identified category ids as (236395, 236399, 246466)
2.with this output
consider these three as parentids
Now i have to find what are category ids for these parent ids

it is 3 level hierarchy

236282---->3(catergory ids)(236395, 236399, 246466)----------->3categoryids
0
srikoteshAuthor Commented:
i am opening new question for this u can suggest ur idea.
0
F PCommented:
The first statement I wrote with the function -- add it to the db, and use that/this (remember to remove the concat when you go live with it -- for visual purposes here):

This is on my live sites

    
SELECT
		CONCAT(REPEAT('    ', level - 1), CAST(id AS CHAR)) AS `category_id`,
		`parent_id`,
		`level`
	FROM (	SELECT
				category_connect_by_parent_eq_prior_id(category_id) AS `id`,
				@level AS `level`
			FROM
				( SELECT
					@start_with := 0,
					@id := @start_with,
					@level := 0
				) AS vars, `ecommerce_categories`
			WHERE
				@id IS NOT NULL
		) AS `ho`
	JOIN
		`ecommerce_categories` AS `hi` ON `hi`.`category_id` = `ho`.`id`;

Open in new window


and here's what you'd get:

1	0	1
    250	1	2
    251	1	2
    253	1	2
    254	1	2
    255	1	2
    256	1	2
    257	1	2
    258	1	2
    259	1	2
    260	1	2
    261	1	2
    262	1	2
    263	1	2
    264	1	2
    265	1	2
    268	1	2
    270	1	2
    271	1	2
    272	1	2
    273	1	2
    274	1	2
    275	1	2
    277	1	2
    278	1	2
    279	1	2
    280	1	2
    281	1	2
    282	1	2
    283	1	2
    284	1	2
    285	1	2
    286	1	2
    287	1	2
    288	1	2
    289	1	2
    290	1	2
    291	1	2
    292	1	2
    293	1	2
    294	1	2
    295	1	2
    296	1	2
    297	1	2
    298	1	2
    299	1	2
    300	1	2
    301	1	2
    302	1	2
    303	1	2
    304	1	2
    305	1	2
    306	1	2
3	0	1
5	0	1
    50	5	2
    51	5	2
    52	5	2
    53	5	2
    54	5	2
    55	5	2
    56	5	2
    57	5	2
    62	5	2
    63	5	2
    64	5	2
    65	5	2
    66	5	2
    68	5	2
    70	5	2
    72	5	2
    73	5	2
    74	5	2
    75	5	2
    76	5	2
    77	5	2
    78	5	2
    79	5	2
    80	5	2
    81	5	2
    82	5	2
    83	5	2
    85	5	2
    86	5	2
    88	5	2
    89	5	2
    90	5	2
    91	5	2
    93	5	2
    94	5	2
    95	5	2
    96	5	2
    97	5	2
    98	5	2
    99	5	2
    100	5	2
    102	5	2
    103	5	2
    104	5	2
    105	5	2
    106	5	2
    107	5	2
    109	5	2
    110	5	2
    112	5	2
    113	5	2
    115	5	2
    116	5	2
    117	5	2
    118	5	2
    123	5	2
    124	5	2
    125	5	2
    126	5	2
    127	5	2
    128	5	2
    131	5	2
    132	5	2
    134	5	2
    135	5	2
    136	5	2
    137	5	2
    424	5	2
    534	5	2
6	0	1
    67	6	2
    84	6	2
    92	6	2
    101	6	2
    108	6	2
    111	6	2
    114	6	2
    129	6	2
    149	6	2
    150	6	2
    151	6	2
    152	6	2
    153	6	2
    154	6	2
    155	6	2
    156	6	2
    157	6	2
    158	6	2
    159	6	2
    160	6	2
    161	6	2
    162	6	2
    163	6	2
    164	6	2
    165	6	2
    166	6	2
    167	6	2
    168	6	2
    169	6	2
    170	6	2
    171	6	2
    172	6	2
    173	6	2
    174	6	2
    175	6	2
    176	6	2
    177	6	2
    178	6	2
    179	6	2
    180	6	2
    181	6	2
    182	6	2
    183	6	2
    184	6	2
    185	6	2
    186	6	2
    187	6	2
    188	6	2
    189	6	2
    190	6	2
    191	6	2
    192	6	2
    193	6	2
    194	6	2
    195	6	2
    196	6	2
    197	6	2
    198	6	2
    200	6	2
    201	6	2
    423	6	2
    425	6	2
    487	6	2
    488	6	2
    489	6	2
    536	6	2
7	0	1
8	0	1
    400	8	2
    401	8	2
    402	8	2
    491	8	2
    531	8	2
130	0	1
133	0	1
483	0	1
484	0	1
    420	484	2
        350	420	3
        351	420	3
        352	420	3
        353	420	3
        354	420	3
        355	420	3
        356	420	3
        357	420	3
        358	420	3
        359	420	3
        360	420	3
        428	420	3
        535	420	3
        537	420	3
        538	420	3
        539	420	3
        540	420	3
        541	420	3
        542	420	3
        543	420	3
    421	484	2
        361	421	3
        362	421	3
        363	421	3
        364	421	3
        365	421	3
        366	421	3
        367	421	3
        368	421	3
        369	421	3
        370	421	3
        371	421	3
        372	421	3
        373	421	3
        374	421	3
        375	421	3
        376	421	3
        377	421	3
        378	421	3
        379	421	3
        380	421	3
        381	421	3
        382	421	3
485	0	1
    58	485	2
    59	485	2
    60	485	2
    61	485	2
486	0	1
    119	486	2
    120	486	2
    121	486	2
    122	486	2
493	0	1
503	0	1
    505	503	2
    506	503	2
    507	503	2
    508	503	2
    509	503	2
    510	503	2
    511	503	2
    512	503	2
    513	503	2
    514	503	2
    516	503	2
    517	503	2
    519	503	2
    520	503	2
    521	503	2
    522	503	2
    523	503	2
    524	503	2
    525	503	2
    526	503	2
    528	503	2
    529	503	2
    530	503	2
    532	503	2
    533	503	2

Open in new window

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.