this is very simple you are doing union operation in the first portion and you are assuming that you are doing union operation of first table and second table ( i think so ).
Now coming back to your query , your query looks in the following format .
select * from t1,t2 but not like select * from t1 union t2.
Hence the query select * from t1,t2 acts like an inner join and will not produce any output . if the query is in the following format it must give you expected output.
correct format:
select * from t1 union t2
Your corrected query :
select * from (select advisor_id,case when coalesce(SUM(commission_Amount),0)<=0 then 0 else SUM(commission_Amount) end as comm
from tbl_advisorcommissions where advisor_id=773 and created_date between '25-sep-2014' and '07-oct-2014' group by advisor_id
union
select advisor_id,case when coalesce(SUM(commission_Amount),0)<=0 then 0 else SUM(commission_Amount) end as comm
from tbl_extra_AdvisorCommissions where advisor_id=773 and created_date between '25-sep-2014' and '07-oct-2014' group by advisor_id) t1
<b>union </b>
(select a.user_id,a.name,a.user_code,pancard_no,case when coalesce(SUM(b.commission_Amount),0)<=0 then 0 else SUM(b.commission_Amount)
end as week1 from tbl_user_registration as a inner join tbl_weeklyCommissions as b on a.user_id=b.advisor_Id where
user_id=773 and b.from_date between '25-sep-2014' and
'07-oct-2014' group by a.user_id,a.name,a.user_code,pancard_no) t2
For more details see
sql joins interview questions and answers or read all
sql server interview questions and answers in http://skillgun.com