Help with query

Hi,

I am having following table in my database
id no name prev_no action
1 3 a 0 join
2 3 a 0 confirm
3 4 b 0 join
4 3 a 0 transfer
5 5 a 3 join
6 4 b 0 confirm
7 5 a 0 perform
8 5 a 0 transfer
9 4 b 0 perform
10 7 a 5 join

The output I want is
id no name prev_no
1 3 a 0
5 5 a 3
10 7 a 5

Could anyone please help me for writing the query?

Your help will be highly appriciated.

Thanks,

Why those 3 rows? What is the logic behind it?

If you look at the table no 3 has three ‘join’ action.

for first join action, no is 3 and the prev_no is 0
for second join action, no is 5 and prev_no is 3
for second join action, no is 10 and prev_no is 5

I can query using where clause (no = 10)

Please help me.

Ok, so if I understand correctly, you want all parents from a given child (starting from no 10, you want to get 5, 3, and 0).
Take a look at this article of our sql expert r937: http://sqllessons.com/categories.html
Please read it and try to understand it, then try to use the logic in your situation. If you are having difficulties, don’t hesitate to ask.