2

Im trying to get all available keys, meaning keys which have been returned (return_date is not null), key hasn't been lost (lost_key = false), and keys never been rented before (id is null). However when I run this I get 0 recirds. If I remove the id condition, I only get keys which have been rented before and all the keys which are not in the keyActivity table are ignored. Does anyone know what I am doing wrong?

SELECT a.key_id, a.drawer_num
FROM 
    keys AS a 
    left outer JOIN keyActivity AS b 
        ON a.key_id = b.key_id
WHERE 
    return_date is not null 
    and lost_key =false
    and id is null;

enter image description here

SOLUTION USED: 

SELECT a.key_id, a.drawer_num
FROM keys AS a LEFT JOIN keyActivity AS b ON a.key_id = b.key_id
WHERE (b.return_date is not null 
and b.lost_key =false) 
OR b.id is null;
Batman
  • 5,563
  • 18
  • 79
  • 155
  • I've tried it a could times with selecting and deselecting the lost attribute. The query seems to catch available keys correctly from what I can see. I've edited the OP with the query used. – Batman Jan 13 '13 at 19:00

1 Answers1

1

id may be a field in both tables, and if this is so, your condition id is null will be interpreted erroneously as the id of keys to be NULL, thus resulting in an empty result set.

It should work if you call it by its full name, i.e. b.id because you want to exclude any previous keyActivity.

However, I also believe you want to have both previous conditions connected by AND and in parentheses, and then the last condition connected by OR because previous key activity is okay as long as the key has been returned.

SELECT * FROM keys AS a LEFT OUTER JOIN keyActivity AS b ON a.key_id = b.key_id
  WHERE (b.return_date IS NOT NULL OR b.id IS NULL) AND a.lost_key = false;

EDIT: I rearranged the conditions, to also catch the case in which the clerk loses a key before ever handing it out.

s.bandara
  • 5,636
  • 1
  • 21
  • 36
  • One table had key_id and the other was Id, so that wasn't the problem. You were write about the OR and the brackets. It worked. Thank you. – Batman Jan 13 '13 at 03:32
  • The lost_key attribute if in the activity table so rearranging it the way you did brings in an error. Someone from another thread suggested that my key table have a lost option also. Any thoughts on that? – Batman Jan 13 '13 at 03:47
  • 1
    Aha, if that's the case you can simply revert to what we had before (with `b.lost_key`) because the clerk can't lose the key in that model. The suggestion you heard somewhere else makes sense to me; it's what I had assumed intuitively. If you want to follow that advice, the edited query will be the right query for you. – s.bandara Jan 13 '13 at 03:52
  • I think i'll leave it the way it is for now. I feel like having it in the activity table provides information as to which user lost the key and the date they signed it out. If a clerk however loses the key, they can delete it using a form ( ill do that later). Does this make sense? – Batman Jan 13 '13 at 03:58
  • haha yea, "what are you talking about, that key never existed" :P – Batman Jan 13 '13 at 03:59
  • One more thing actually. I pasted the sql for this query into a loopup field for key_id in the activity table. However I noticed that if I make certain keys as lost, I need to close the table and open it again in order for those keys to no longer by listed in the look up. Is there some way to make it instantaneous. This is my file if you have time to take a look: http://jumpshare.com/b/97n2eY – Batman Jan 13 '13 at 04:02
  • 1
    I think this is specific to your RDBMS, not related to SQL. Sorry I have to pass on that one. – s.bandara Jan 13 '13 at 04:03