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

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
/

Select allOpen in new window

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.

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

Select allOpen in new window

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.

Sorry.  What I posted was for Oracle.  I didn't see the MySQL zone.

I cannot help with MySQL.

.... 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 ;

Select allOpen 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`;

Select allOpen 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

Select allOpen in new window

Thanks for ur suggestion.

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? .

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)

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

Select allOpen in new window

or the other way I mentioned:

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

Select allOpen in new window

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

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

i am opening new question for this u can suggest ur idea.

https://www.experts-exchange.com/questions/28662567/how-can-i-write-a-relationship-query-in-an-effecive-way.html

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`;

Select allOpen 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

Select allOpen in new window