使用查询获取父级和子级数据

Question

I have 2 tables.

(1) taxes: Parent table

id title
1   service tax
2   vat

(2) cess_taxes: Child table

id  tax_id   title
1      2    cess vat
2      1    cess service
3      1    add. service

I want below data,

id title*
1 service tax (parent)
2 cess service (child)
3 add. service (child)
4 vat (parent)
5 cess vat (child)

How can i get data with using query???

I have tried with left, right & inner join

SELECT t.id, t.title, c.id, c.title
FROM `taxes` AS t
LEFT JOIN cess_taxes AS c ON c.tax_id = t.id

Answer 1

SET @rn:=0;
SELECT @rn:=@rn+1 AS rank, title,id
FROM (
  SELECT title,id
  FROM taxes

  Union

  SELECT c.title,
         case when t.id=1 then 1.5 else 3 end 
  FROM cess_taxes c
  inner join taxes t
    on t.id=c.tax_id


) t1
Order By id

SQL Fiddle Demo