Q:

Not getting desired output in mysql

I am firing a query in mysql but not getting desired output.
this is the code:
select team_name,
       sum(semis.points+final.points) as final_points 
from semis 
inner join final on semis.sid=final.sid 
inner join teams on teams.tid=semis.tid 
group by semis.tid 
   union 
select team_name,
       semis.Points 
from semis 
inner join teams on semis.tid=teams.tid 
left join final on semis.sid=final.sid 
where final.sid is null;

OUTPUT:
+-----------------------+--------------+
| team_name             | final_points |
+-----------------------+--------------+
| BioTech & BioChem     |            7 |
| Chemistry             |            7 |
| Botany & Zoology      |            7 |
| Physics & Electronics |           17 |
| BCA                   |           19 |
| BCOM                  |           11 |
| Gujarati              |           10 |
| English               |           10 |
| Economics             |           20 |
| BCOM                  |            3 |
| Chemistry             |            3 |
| English               |            3 |
+-----------------------+--------------+

and the result i want to fetch
+-----------------------+--------------+
| team_name             | final_points |
+-----------------------+--------------+
| BioTech & BioChem     |            7 |
| Chemistry             |           10 |
| Botany & Zoology      |            7 |
| Physics & Electronics |           17 |
| BCA                   |           19 |
| BCOM                  |           14 |
| Gujarati              |           10 |
| English               |           13 |
| Economics             |           20 |
+-----------------------+--------------+

Adding last 3 values to english,bcom,chemistry increasing it by 3 and making a total of BCOM: 14, Chemistry:10 , English: 13 

A:

From the sample data you posted and the expected results it looks like you  can do it without UNION, by left joining final and with coalesce() for final.points:
select team_name, sum(semis.points + coalesce(final.points, 0)) as final_points 
from semis 
inner join teams on teams.tid=semis.tid 
left join final on semis.sid=final.sid 
group by semis.tid 

