Creating Null Values For Empty Rows

[Jdbenike]

I have some code that Iam struggling with. It works fine until I add a column from different table called (d.commenttext).

Originally this code below brought back thousands of rows. Yet, when I connected the (d.commenttext) field from the (vp_tmshtcommments d) table it only brings back a few records. It's because in that (vp_tmshtcomments d) table there is only comments once in a while. So most of the time, there is no value that exists and it won't bring back all those row that showing originally in this report.

I understand why it does this. Yet, I am trying to figure out a way to create a 'NULL' value for all those records that dont' exist so it still brings back the same amount of data that it did before I added the (d.commenttext) field and linked it to the report with the (vp_tmshtcomments d) table with this part of the code (and a.personnum = d.personnum and a.applydate = d.eventdate)

Here is the code below.

select	b.homelaboracctdsc,
    b.homelaborleveldsc1 + '/' + b.homelaborleveldsc2 + '/' + b.homelaborleveldsc3 + '/' + b.homelaborleveldsc4 + '/' + b.homelaborleveldsc5 as HomeLaborDesc,
	a.personnum,
	a.personfullname,
	a.paycodename,
    c.payrulename,
	sum(a.timeinseconds / 60) as Hours,
	sum(a.wageamount) as Wages,
    sum(a.moneyamount) as Wages2
    d.commenttext
from vp_totals a,
	 vp_person b,
     vp_employee c, 
     vp_tmshtcomments d
where a.personnum = b.personnum
and b.personnum = c.personnum
and a.personnum = d.personnum
and a.applydate = d.eventdate
and	(a.applydate >= getdate()-7
and	a.applydate <=    getdate())
and	b.homelaborlevelname2 = 'D12'
and	b.homelaborlevelname3 = '06882'
group by b.homelaboracctdsc,
         b.homelaborleveldsc1,
         b.homelaborleveldsc2, 
         b.homelaborleveldsc3,
         b.homelaborleveldsc4,
         b.homelaborleveldsc5,
         a.personnum,
	     a.personfullname,
	     a.paycodename,
         c.payrulename,
         d.commenttext

To restate my issue one more time. The field above sometimes does not have a line or record for the (d.commenttext) so I need a way to still bring back all the original data, and only fill that column unless if data exists in the the table where that field resides. I know it's a rookie question. Please help. Much thanks will be rewarded.

 

[ousoonerjoe]

Without actually pulling it in and testing, my first suggestion would be to use a LEFT JOIN. It should fill in the missing data from the table with NULL.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[Jdbenike]

correct sir! thank you

 

[SQLSister]

assuming the last table is the one you added:

select    b.homelaboracctdsc,    
b.homelaborleveldsc1 + '/' + b.homelaborleveldsc2 + '/' + b.homelaborleveldsc3 + '/' + b.homelaborleveldsc4 + '/' + b.homelaborleveldsc5 as HomeLaborDesc,    
a.personnum,    
a.personfullname,    
a.paycodename,    
c.payrulename,    
sum(a.timeinseconds / 60) as Hours,    
sum(a.wageamount) as Wages,    
sum(a.moneyamount) as Wages2,    
d.commenttext
from vp_totals a     
JOIN vp_person b  ON a.personnum = b.personnum  
JOIN vp_employee c  ON  b.personnum = c.personnum  
LEFT JOIN vp_tmshtcomments d ON a.personnum = d.personnum and a.applydate = d.eventdate
where     (a.applydate >= getdate()-7
and    a.applydate <=    getdate())
and    b.homelaborlevelname2 = 'D12'
and    b.homelaborlevelname3 = '06882'
group by b.homelaboracctdsc,         
b.homelaborleveldsc1,         
b.homelaborleveldsc2,          
b.homelaborleveldsc3,         
b.homelaborleveldsc4,         
b.homelaborleveldsc5,         
a.personnum,         
a.personfullname,         
a.paycodename,         
c.payrulename,         
d.commenttext

Promise that you will never ever under any circumstances use implied joins again. Very bad practice.

"NOTHING is more important in a database than integrity." ESquared