MySQL change select output with data from other row

Question

I'm sorry if I don't know the correct terminology but here we go:

I have a table with the following columns and rows:

------------------------------
| id    | parent_id  | type  |
------------------------------
| 1     | 0          | text1 |
| 2     | 1          | text2 |
------------------------------

Now what I want is to select the type, but if the parent_id is not 0 I want the type to be the content of the type from it's parent +[ the type of itself ].

So the output for a select would in this case be:

----------------
| type         |
----------------
| text1        |
| text1[text2] |
----------------

I was trying this with a concat and an if statement, but couldn't figure this out.

Can you help me out?

Will this only be for the immediate parent, or do you want to include the ultimate ancestor? (The latter is problematic in MySQL.) — user359040
– user359040, Commented Apr 11, 2013 at 9:42

user359040 · Accepted Answer · 2013-04-11 09:37:11Z

4

Try:

select c.id,
       case c.parent_id 
            when 0 then c.type
            else concat(p.type,'[',c.type,']')
       end as type
from mytable c
left join mytable p on c.parent_id = p.id

SQLFiddle here.

answered Apr 11, 2013 at 9:37

user359040

Sign up to request clarification or add additional context in comments.

3 Comments

Mihai Matei Over a year ago

shouldn't be case parent_id there?

BonifatiusK Over a year ago

Super thanks! Now can you also do a where here? or is this not possible because of the if statement in there?

user359040 Over a year ago

@BonifatiusK: You should be able to do a where here.

Collectives™ on Stack Overflow

MySQL change select output with data from other row

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related