is there a way to display only child if it exists or else only parent data

user_13115532 0
Viewed 75

I have two tables and one is parent table and second one is child table both tables has transactions but where as some transactions will take place in parent table and some in child based on their child existence if the parent row doesn't have any child it will be the result and if the parent has the child the child table itself will be the result some times both may occur i tried but not getting the exact solution please help.....

1st table and Parent table

id item amount tax status
1 4 20 2 Y
2 5 15 1 N
3 6 5 0 N

2nd table and child table

id item p_id amount tax
1 1 1 10 1
2 2 2 10 1
3 3 1 15 1

3rd table

id item
1 mobile
2 heater
3 mouse
4 electronic
5 software
6 papers

What I want is

item amount tax
mobile 10 1
heater 10 1
mouse 15 1
software 15
papers 5 0
mysql
2 Answers
SELECT COALESCE(child.item, parent.item) item,
       COALESCE(child.amount, parent.amount) amount,
       COALESCE(child.tax, parent.tax) tax
FROM parent
LEFT JOIN child ON parent.id = child.p_id
user_101387340 
SELECT IFNULL(child.item, parent.item) AS item, 
       IFNULL(child.amount, parent.amount) AS amount,
       IFNULL(child.tax, parent.tax) AS tax
FROM parent
LEFT JOIN child ON parent.id = child.p_id
user_119062910
Related