CODEWITHSUNDEEP
mysql
ajm
ajm

Reputation: 13203

How to write a mysql query to get the desired data?

I have table like this 

id seq is_child type
a   0   0   1
b   1   0   1
c   2   0   1
d   3   0   1
e   4   0   2
f   0   1   1
g   1   1   1
h   2   1   1
i   5   0   1
j   6   0   1

It has four columns Id, sequence and is_child column and type.

Parent and child are stored in same table with is_child column value set to 1 for children.

For parents type will be 2.

Sequence starts from 0 and increments by 1. For children sequence again starts from 0 and increments by 1.

As you can see in table id e is parent and it has three children which are f, g , h with seq 0, 1 and 2 resp.

We have another table to maintain this parent child relationship

parent Id   child id
e   f
e   g
e   h

With this data, how do i fire a query to get result shown below.

id  seq
a   0
b   1
c   2
d   3
f   4
g   5
h   6
i   7
j   8

So, basically I dont want parents in result and the sequence should be as shown.

Please help.

Upvotes: 0

Views: 44

Answers (2)

Nailgun
Nailgun

Reputation: 4169

You can join your nodes with relations table (http://sqlfiddle.com/#!2/2e767/7):

SET @n = -1;
SELECT t.id, @n:=@n+1 seq FROM (
    SELECT DISTINCT IFNULL(c.id, p.id) id
    FROM node p
    LEFT JOIN relation r ON r.parent_id = p.id
    LEFT JOIN node c ON c.id = r.child_id
) t;

Upvotes: 1

O. Jones
O. Jones

Reputation: 108641

Without completely understanding your parent/child requirements, I think the result set you need may be available by doing this. (http://sqlfiddle.com/#!2/4da58/1/0)

select id, @var:=@var+1 seq
  from t1,
  (select @var:=-1) init
 where type <> 2
 order by id

This assigns a row number, starting with zero, to each non-parent item in your table.

Upvotes: 0

Related Questions