inner and outer joins

[redtidewater]

I am very new to sql programming. I am trying to sql data from a database that has 2 table with labor information in it and join the data output together. Below I have included how I want the end result to be. But I cant seem to figure out how to join the emp_id and dates so that if there is no data it will put in 0's My select statement has "IFNULL(reg_labor.reg_hrs) 0,reg_labor.reg_hours) AS Reghrs" for all the field. Any suggestions would be appreciated. I am sure I am missing something simple.

This is what I have for a where clause:

where lab.emp_id = vac.emp_id AND
lab.date =* vac.date


Table 1: reg_labor lab

reg_labor.emp_id
reg_labor.reg_hrs
reg_labor.ovt_hrs
reg_lab.date

Table 2: vacation_labor vac

Vaction_labor.emp_id
vaction_labor.labor_hours
vaction_labor.lab_pay
vacation_labor.date

My problem is there will be records that are not in one table or the other but I need to fill them with 0's when there is nothing in the table. Here is the results I would like:

emp_id date reg_hrs reg_pay lab_hours lab_pay

1 12_1_2008 8 80 0 0
1 12_02_2008 0 0 8 80
1 12_03_2008 4 40 4 40
2 12_1_2008 8 80 0 0

 

[PDreyer]

The correct ASE syntax is
ISNULL(reg_labor.reg_hrs,0)
or
coalesce(reg_labor.reg_hrs,0)

I also suggest you use the left join syntax as apposed to the old stype *=

However it would appear that you actually want a union
e.g.

select 
 reg_labor.emp_id  
,reg_labor.date
,reg_labor.reg_hrs
,reg_labor.reg_pay 
,0 as lab_hours 
,0 as lab_pay
from reg_labor
union all
select 
 Vaction_labor.emp_id
,vacation_labor.date
,0
,0
,vaction_labor.labor_hours
,vaction_labor.lab_pay
from vacation_labor
order by 1,2