cannot get unlinked records

[Claus Mygind]

I have two tables employee and time. Both contain the common field EMPNO.

I want to link the tables on the EMPNO and find all records within a specific date range. But I also want to include any employee who has no time records in the time file.

note - my tables are setup to have "not null" values in the table columns. The default value is ''

here are my attempts to construct the select statement to no avail

select e.*, t.*
 from employee e
 left join time t
  on e.empno = t.empno
 where (t.workday >= '20090208' and t.workday <= '20090214')
  or t.empno = ''

I only get the records that are within the date range and not the employees with no records in the time file.

I can get all records from the time file and the employees with no time records like this. (but that is no good because it includes time records outside the date range)

select e.*, t.*
 from employee e
 right join time t
  on e.empno = t.empno

[Atli]

Hi.

You could try something like:
[code=mysql]SELECT *
FROM employee AS e
LEFT JOIN time AS t
ON e.empno = t.empno
WHERE t.workday BETWEEN '2009-02-08'
AND '2009-02-14'
OR t.workday IS NULL;[/code]
When you use a LEFT JOIN, even if a row in the parent table does not have a matching row in the joined table, the row is still added. It just has NULL values for each column in the joined table.

So, by adding the OR ... IS NULL clause, we instruct it to include those rows as well as those that match the range.

[Claus Mygind]

Hi.

You could try something like:
[code=mysql]SELECT *
FROM employee AS e
LEFT JOIN time AS t
ON e.empno = t.empno
WHERE t.workday BETWEEN '2009-02-08'
AND '2009-02-14'
OR t.workday IS NULL;[/code]
When you use a LEFT JOIN, even if a row in the parent table does not have a matching row in the joined table, the row is still added. It just has NULL values for each column in the joined table.

So, by adding the OR ... IS NULL clause, we instruct it to include those rows as well as those that match the range.

I tried that and still did not get the records without matches.

[Atli]

Ok.

Could you post the exact structure of your tables here?

I tried the example I posted on a simple test database on my local server and it worked fine, although there is not way I can accurately test this without knowing exactly how your tables are structured.

[Claus Mygind]

Ok.

Could you post the exact structure of your tables here?

I tried the example I posted on a simple test database on my local server and it worked fine, although there is not way I can accurately test this without knowing exactly how your tables are structured.

Yes. see my structure below. The problem is not getting the null records if I want all records. The problem occurs when I want to limit the records in the child table with the "where" clause. When I include a date range in the where clause along with the "or" null record matches in the parent table I still get only the matching records and not any null records.

employee table
RECKEY varchar(34) not null default value ' '
EMPNO varchar(4) not null default value ' '
FIRST varchar(20) not null default value ' '
LAST varchar(20) not null default value ' '
LOCATION varchar(30) nut null default value ' '

2 indexes primary = RECKEY employee = EMPNO
RECKEY = location+empno

timeRecs table
RECKEY varchar(22) not null default value = ' '
EMPNO varchar(4) not null default value = ' '
WORKDAY date not null default value = '0000-00-00'
JCODE varchar(2) not null default value=' '
JOBNO char(6) not null
HOURS decimal(4,2) not null default value='0.00'
MILES decimal(7,1) not null default value='0.0'
TOLLS decimal(5.2) not null default value='0.00'
IDDEPT enum('T','F') not null default value='T'

1 index RECKEY=empno+date+jobno+jcode


select  t.*,  e.*, concat(e.last, ',', e.first) fullname
 from employee e
 left join timeRecs t on e.empno=t.empno
 where (t.workday >= '20090208' and t.workday <= '20090214')
  or t.empno = null
 order by e.last, e.first, t.reckey

[Claus Mygind]

I tried that and still did not get the records without matches.

Just an update here. I looked at your suggested code and I have discovered some subtleties that I had over looked before (assuming that the code was exactly alike). So your suggestion does make sense and I am almost there.

Here are some known facts
employee table = 174 records

time2 table = 141 records
of which there are 33 records
between 2/08/2009 and 2/14/2009 inclusive

when I group the 33 records they belong to only 6 employees

So I should like to see a list of 174 employees of those 6 employee records
should have related child (time entered) records.

I have noticed that the following is not interpreted the same:

where t.empno = null
and
where t.empno is null

only the later returns a rowset.

also

left join timeRecs t on e.empno = t.empno
and
join timeRecs t on e.empno = t.empno

only the former works correctly

The following code yields 169 rows

 
select t.empno, e.empno
 from employee e
 left join time2 t on e.empno=t.empno
 where t.empno is null
   or (t.workday between '20090208' and '20090214')
 group by e.empno

result = 169 (seems almost right remember 174 in employee table)

I have determined that the 5 missing records are employees with no time records and that they seem spread out in the table (ie they are not clustered in one location of the table)

I even added at "order by e.empno" clause on which I have an index and it still did not help.

[Atli]

So I should like to see a list of 174 employees of those 6 employee records
should have related child (time entered) records.

Keep in mind that your query only returns employees that have no records, or that have records that match your time frame.

Employees that have records, but none of which match your time frame will be omitted from the list.

Could that be causing the mismatch?

Try including your time check in the ON clause.
[code=mysql]
SELECT t.empno, e.empno
FROM employee e
LEFT JOIN time2 AS t
ON e.empno = t.empno
AND t.workday BETWEEN '20090208' AND '20090214'[/code]

I have noticed that the following is not interpreted the same:

where t.empno = null
and
where t.empno is null

only the later returns a rowset.

In SQL servers, NULL is not actually a value. It represents a non-value: a missing piece of data.

It is a common mistake to equate empty values, such as empty strings or the number 0, to NULL.
But in reality they are not the same thing. Even an empty value is in fact a value, and can therefore not be NULL.

The comparison operator (=) is meant to compare values. NULL, not being a value, can not be compared like a value, but has to be used with the IS NULL operators.

also

left join timeRecs t on e.empno = t.empno
and
join timeRecs t on e.empno = t.empno

only the former works correctly

LEFT JOIN is specifically designed to include all rows, even if there are no matching rows in the table that is being joined with the parent table.

A normal JOIN (a.k.a. INNER JOIN or CROSS JOIN) is designed to omit any row that does not fulfill the conditions set in the ON clause.

As a result, when you specify a PK = FK relationship, a LEFT JOIN will return all rows in the parent table, giving NULL fields for rows that do not have matching rows in the joining table, while a normal JOIN will only return rows that do have matching rows int he joining table.

So for your purpose, a normal JOIN will not work.

[Claus Mygind]

Thanks for all the terrific advice. I think I can get it now