How to get parent name through MYSql query

I have following query :

SELECT ty.id, ty.type, ty.pid, if(ty.pid = 0, '-',
       (select ty.type from bid_type ty where ty.pid != 0 and ty.id = ty.pid)) as parent,
       ty.code, ty.description, ty.icon,
       date_format(ty.adate, '%m-%d开发者_运维知识库-%Y %H:%i:%s') as adate,
       date_format(ty.edate, '%m-%d-%Y %H:%i:%s') as edate, ty.status
  FROM (bid_type ty)

I want to get 'parent' of child through this query. But it is returning empty result for 'parent'. Can some one guide me what Iam doing wrong and how it can be rectified.

Thanks in advance

Try giving the two tables different aliases. In your code, bid_type is aliased to ty twice.

You need to alias your tables properly to get the data you want. Also, instead of using a sub-query to select the parent type, you should use a JOIN. Probably an outer join since your query implies that the parent-child relationship in this table is optional.

This should work for you:

SELECT ty.id, ty.type, ty.pid, 
  if(ty.pid = 0, '-', p.type) as parent,
  ty.code, ty.description, ty.icon,
  date_format(ty.adate, '%m-%d-%Y %H:%i:%s') as adate,
  date_format(ty.edate, '%m-%d-%Y %H:%i:%s') as edate, ty.status
FROM bid_type ty
  LEFT OUTER JOIN bid_type p on p.id = ty.pid