CODEWITHSUNDEEP
phpmysql
user3668438
user3668438

Reputation: 165

Get records in two lines with mysql

I have some data in multiple tables i want to join that tables and need to display the result as multiple records below is the table structure

          main_table                           
   id     name   height                
   1      test1  5.2                     
   2      test2  4.6                    

          child_table                         
   id  main_table_id  name   height     
   1       1          test3  5.3         
   2       1          test4  4.5

expecting result like

   id  name    height   

   1   test1   5.2      
   1   test3   5.3
   1   test4   4.5

How can i achive this using query in MySql expecting suggestions how can i achieve this?

Upvotes: 0

Views: 32

Answers (2)

Fahmi
Fahmi

Reputation: 37473

Try using left and colasce

select maintable.id,COALESCE(maintable.name,childtable.name) as name,
COALESCE(maintable.height,childtable.height) 
from maintable left join childtable
on maintable.id=childtable.main_table_id
where maintable.id=1

Upvotes: 0

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28844

Use UNION

SELECT id, name, height 
FROM main_table 
WHERE id = 1

UNION 

SELECT main_table_id as id, name, height 
FROM child_table 
WHERE main_table_id = 1

Upvotes: 1

Related Questions