By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
463,061 Members | 487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 463,061 IT Pros & Developers. It's quick & easy.

cannot get unlinked records

Claus Mygind
100+
P: 571
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
Expand|Select|Wrap|Line Numbers
  1. select e.*, t.*
  2.  from employee e
  3.  left join time t
  4.   on e.empno = t.empno
  5.  where (t.workday >= '20090208' and t.workday <= '20090214')
  6.   or t.empno = ''
  7.  
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)
Expand|Select|Wrap|Line Numbers
  1. select e.*, t.*
  2.  from employee e
  3.  right join time t
  4.   on e.empno = t.empno
  5.  
Feb 19 '09 #1
Share this Question
Share on Google+
7 Replies

Atli
Expert 5K+
P: 5,058
Hi.

You could try something like:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM employee AS e
  3. LEFT JOIN time AS t
  4.   ON e.empno = t.empno
  5. WHERE t.workday BETWEEN '2009-02-08' 
  6.                 AND '2009-02-14'
  7. OR t.workday IS NULL;
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.
Feb 20 '09 #2

Claus Mygind
100+
P: 571
@Atli
I tried that and still did not get the records without matches.
Feb 21 '09 #3

Atli
Expert 5K+
P: 5,058
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.
Feb 22 '09 #4

Claus Mygind
100+
P: 571
@Atli
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.

Expand|Select|Wrap|Line Numbers
  1. employee table
  2. RECKEY varchar(34) not null default value ' '
  3. EMPNO varchar(4) not null default value ' '
  4. FIRST varchar(20) not null default value ' '
  5. LAST varchar(20) not null default value ' '
  6. LOCATION varchar(30) nut null default value ' '
  7.  
  8. 2 indexes primary = RECKEY employee = EMPNO
  9. RECKEY = location+empno
  10.  
  11. timeRecs table
  12. RECKEY varchar(22) not null default value = ' '
  13. EMPNO varchar(4) not null default value = ' '
  14. WORKDAY date not null default value = '0000-00-00'
  15. JCODE varchar(2) not null default value=' '
  16. JOBNO char(6) not null
  17. HOURS decimal(4,2) not null default value='0.00'
  18. MILES decimal(7,1) not null default value='0.0'
  19. TOLLS decimal(5.2) not null default value='0.00'
  20. IDDEPT enum('T','F') not null default value='T'
  21.  
  22. 1 index RECKEY=empno+date+jobno+jcode
  23.  
  24.  
  25. select  t.*,  e.*, concat(e.last, ',', e.first) fullname
  26.  from employee e
  27.  left join timeRecs t on e.empno=t.empno
  28.  where (t.workday >= '20090208' and t.workday <= '20090214')
  29.   or t.empno = null
  30.  order by e.last, e.first, t.reckey
  31.  
Feb 23 '09 #5

Claus Mygind
100+
P: 571
@Claus Mygind
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
Expand|Select|Wrap|Line Numbers
  1.  
  2. select t.empno, e.empno
  3.  from employee e
  4.  left join time2 t on e.empno=t.empno
  5.  where t.empno is null
  6.    or (t.workday between '20090208' and '20090214')
  7.  group by e.empno
  8.  
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.
Feb 24 '09 #6

Atli
Expert 5K+
P: 5,058
@Claus Mygind
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT  t.empno, e.empno
  2. FROM    employee e
  3. LEFT JOIN time2 AS t 
  4.     ON  e.empno = t.empno
  5.     AND t.workday BETWEEN '20090208' AND '20090214'
@Claus Mygind
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.

@Claus Mygind
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.
Feb 24 '09 #7

Claus Mygind
100+
P: 571
Thanks for all the terrific advice. I think I can get it now
Mar 2 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.