How Do I Do Select Records From Two Table When One Table Is Null

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

In my query table t1,t2 is not null it gives result,but if t1 is null and t2 is not null its given also null

this is my query.

SQL

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,

(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

Output:

when t1 & t2 is not null

advisor_id comm user_id name user_code pancard_no week1
773 2000 773 A.MARY BMG-501030 NULL 1350

if t1 is null

it returns No value but t2 is not null

advisor_id comm user_id name user_code pancard_no week1

pls give some idea.

Thank you !...

Posted 10-Oct-14 3:11am

Maddy selva

Add a Solution

Comments

PhilLenoir 10-Oct-14 10:05am

Your schema makes no sense to me. How do tbl_advisorcommissions and tbl_extra_advisorcommissions relate tbl_user_registration or tbl_weeklyCommissions?
It would help immensely when you pose a question if you format your code with indents and line breaks.

A cross join returns no rows when one side has no rows (a cross join is a straightforward Cartesian product of the number of rows in each joined row set and 0 x n is always 0).

Please note that there is redundancy in the expressions where you are summing commission amount; your case and you coalesce are doing exactly the same thing and could be replaced by the simpler "IsNull(SUM(commission_amount),0)"

3 solutions

Solution 1

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 :

SQL

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

Posted 10-Oct-14 4:19am

pallelokanathareddy

Updated 10-Oct-14 4:23am

v2

Comments

Maddy selva 10-Oct-14 10:55am

It is not possible, because t1 and t2 have different columns,
If union wants same column on both table.

Solution 2

In any type of join (you are using ansi format join) if one table rows are nulls then other table values will not be displayed in result set which are participating in join .

Posted 10-Oct-14 5:02am

pallelokanathareddy

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

King Fisher · Accepted Answer · 2014-10-10T20:30:00

use Count Aggregate function it will Return value If the Table doesn't Have any values

SQL

create table #table(id   int identity(1,1),advisor_id int,amt float)

insert into #table values (1,100)
insert into #table values (1,200)
insert into #table values (1,300)

select *from (select count(advisor_id)as a,isnull(sum(amt),0)as b from #table where advisor_Id=2)t1,( select 2 as b)t2