UNION ALL of two datasets not bringing back data from first dataset

Hi,

I created a UNION ALL between two datasets. An employee has the ability to record time in two different locations (sets of tables). The UNION works great as long as the employee is identified in both datasets. But when an employee only has data in the second dataset then no result set is returned in Crystal for that employee. I though the "UNION ALL" command (I also tried UNION) just appends the two datasets together?

If that's the case why does the employee not show at all in the report even though they have records in the second dataset?

I'm using CR 11.

Below is the sql.

thanks in advance!

Mark

Select ft.employee_timesheets_id, ft.entry_date, ft.employee_roles_id, ft.quantity, ft.charge_units_id, ft.deleted_at, ft.pay_rate, ft.field_tickets_id, field_tickets.sequence, employee_roles.employee_role_name, employee_timesheets.employee_timesheets_id, employee_timesheets.sequence, pay_periods.period_start_date, pay_periods.period_end_date, employees.employees_id, employees.employee_first_name, employees.employee_last_name, employees.countries_id, calendar_date.caldate, pay_periods.pay_periods_id, customers.customer_name, employee_timesheets.employee_comments, employee_timesheets.office_comments, company_offices.office_name, ft.employee_timesheet_field_ticket_entry_id, charge_units.charge_units_duration, employee_timesheet_statuses.employee_timesheet_status_name from employee_timesheet_field_ticket_entry AS ft

INNER JOIN calendar_date ON ft.entry_date=calendar_date.caldate

INNER JOIN field_tickets ON ft.field_tickets_id=field_tickets.field_tickets_id

INNER JOIN jobs ON field_tickets.jobs_id=jobs.jobs_id

INNER JOIN customers ON jobs.customers_id=customers.customers_id

INNER JOIN employee_roles ON ft.employee_roles_id=employee_roles.employee_roles_id

INNER JOIN employee_timesheets ON ft.employee_timesheets_id=employee_timesheets.employee_timesheets_id

INNER JOIN pay_periods ON employee_timesheets.pay_periods_id=pay_periods.pay_periods_id

INNER JOIN employees ON employee_timesheets.employees_id=employees.employees_id

INNER JOIN company_offices ON jobs.company_offices_id=company_offices.company_offices_id

INNER JOIN charge_units ON ft.charge_units_id=charge_units.charge_units_id

INNER JOIN employee_timesheet_statuses ON employee_timesheets. employee_timesheet_statuses_id=employee_timesheet_statuses.employee_timesheet_statuses_id where

calendar_date.caldate>='2012-12-23' and

calendar_date.caldate<='2013-12-22' and

ft.entry_date>='2012-12-23' and

ft.entry_date<='2013-12-22' and

pay_periods.subscriptions_id=2 and

employees.companies_id=2 and

(employees.employee_last_name LIKE 'A%' or employees.employee_last_name LIKE 'B%' or employees.employee_last_name LIKE 'C%' or employees.employee_last_name LIKE 'D%' or employees.employee_last_name LIKE 'E%' or employees.employee_last_name LIKE 'F%') and

IsNull (ft.deleted_at)

UNION ALL

Select mt.employee_timesheets_id, mt.entry_date, mt.employee_roles_id, mt.quantity, mt.charge_units_id, mt.deleted_at, mt.pay_rate, 0, 0, employee_roles.employee_role_name, employee_timesheets.employee_timesheets_id, employee_timesheets.sequence, pay_periods.period_start_date, pay_periods.period_end_date, employees.employees_id, employees.employee_first_name, employees.employee_last_name, employees.countries_id, calendar_date.caldate, pay_periods.pay_periods_id, customers.customer_name, employee_timesheets.employee_comments, employee_timesheets.office_comments, company_offices.office_name, mt.employee_timesheet_entry_id, charge_units.charge_units_duration, employee_timesheet_statuses.employee_timesheet_status_name from employee_timesheet_entry AS mt

INNER JOIN calendar_date ON mt.entry_date=calendar_date.caldate

LEFT JOIN jobs ON mt.jobs_id=jobs.jobs_id

LEFT JOIN field_tickets ON mt.jobs_id=field_tickets.jobs_id

INNER JOIN customers ON jobs.customers_id=customers.customers_id

INNER JOIN employee_roles ON mt.employee_roles_id=employee_roles.employee_roles_id

INNER JOIN employee_timesheets ON mt.employee_timesheets_id=employee_timesheets.employee_timesheets_id

INNER JOIN pay_periods ON employee_timesheets.pay_periods_id=pay_periods.pay_periods_id

INNER JOIN employees ON employee_timesheets.employees_id=employees.employees_id

INNER JOIN company_offices ON jobs.company_offices_id=company_offices.company_offices_id

INNER JOIN charge_units ON mt.charge_units_id=charge_units.charge_units_id

INNER JOIN employee_timesheet_statuses ON employee_timesheets. employee_timesheet_statuses_id=employee_timesheet_statuses.employee_timesheet_statuses_id where

calendar_date.caldate>='2012-12-23' and

calendar_date.caldate<='2013-12-22' and

mt.entry_date>='2012-12-23' and

mt.entry_date<='2013-12-22' and

pay_periods.subscriptions_id=2 and

employees.companies_id=2 and

(employees.employee_last_name LIKE 'A%' or employees.employee_last_name LIKE 'B%' or employees.employee_last_name LIKE 'C%' or employees.employee_last_name LIKE 'D%' or employees.employee_last_name LIKE 'E%' or employees.employee_last_name LIKE 'F%') and

IsNull (mt.deleted_at)

order by employee_last_name asc, employee_first_name, entry_date, employee_timesheet_field_ticket_entry_id