Query table using data from same table

[rasco22862]

Hello, I have the following Mail table:

Id Number Type Description mailDate User
4 101 Inbox Aaaa 22/04/2010 Reception
5 102 Inbox Bbbb 22/04/2010 Reception
6 103 Inbox Cccc 23/04/2010 Reception
7 102 Received Dddd 24/04/2010 John

"Inbox" is the mail that arrives at the office, and "Received" is when the destination person actually GETS the mail. So the recepcionist enters the inbox data, and then the destination person enters the received data when he pickup the mail from the receptionist.
I want to filter all "Inbox" records that weren´t "Received". I`m cofused on how to achieve this.

Thanks!

 

[PKJ]

Try if this works for you -

SELECT A.Id, A.Number, A.Type
FROM Mail_Table As A, Mail_Table As B
WHERE ( A.Number = B.Number And A.Type = "InBox" And B.Type <>"Received")

-PKJoshi

 

[rasco22862]

Try if this works for you -

SELECT A.Id, A.Number, A.Type
FROM Mail_Table As A, Mail_Table As B
WHERE ( A.Number = B.Number And A.Type = "InBox" And B.Type <>"Received")

-PKJoshi

Thanks PKJ. It almost worked! But you inspire me to get a not-so-elegant solution. Here it is:

SELECT Mail.Id, Mail.Number, Mail.Type, Mail.Description, Mail.mailDate
FROM Mail
WHERE Mail.Id NOT IN (SELECT A.Id
FROM Mail AS A, Mail AS B
WHERE (((A.Number)=.[Number]) AND ((B.Type)="Received")));

 

[PKJ]

Hi rasco22862,

I admit that I did not care for elegancy and just put to words the idea that came to my mind without even trying that. After all I am a beginner and am not a professional programmer. Your suggestion/improvement is instructive for me. Thanks for that.
Wish you good time. - PKJoshi.

 

[highandwild]

Would it be simplier if each email message was recorded in just one record and that record just updated when subsequent events relating to it occured.

 

[PKJ]

highandwild wrote:

Would it be simplier if each email message was recorded in just one record and that record just updated when subsequent events relating to it occured.

Wouldn't that lead to unelegant-design problem.