SQL Problem

[daveshack]

Hi. I am trying to write a query that will return rows from TableA if Rows in TableB do not exist. TableA holds data until a specified date and then it is transferred to TableB.

For Example:
TableA
Fields1 - Primary Key
Fields2 - FK to TableB.Fields2
Fields3 - FK to TableB.Fields3
Fields4 - Integer

TableB
Fields1 - Primary Key
Fields2 - FK to TableA.Fields2
Fields3 - FK to TableA.Fields3
Fields4 - Date

Now I want to return all rows in Table A do not have corresponding rows in TableB. So that if the following 3 conditions are true - a row in TableA will NOT be returned.

TableA.Fields2 = TableB.Fields2
TableA.Fields3 = TableB.Fields3
adddate('Date Value', TableA.Fields4) = TableB.Fields4

TableA has a primary key but the combination of Fields2 and Fields3 will be unique. This is also the case in TableB.
TableA.Fields4 will vary. Therefore for each row it must be evaluated if the TableB.Fields4 will equal the calculated adddate value.

I am using VB6 and MySQL v4.1.1a

I have toyed around with EXISTS and IN statements in my attempts but to no avail as yet. Any suggestions would be appreciated.

Thanks

Dave

[techgnome]

SELECT A.*
FROM TableA A
LEFT JOIN TableB B
  ON A.Fields2 = B.Fields2
    AND A.Fields3 = B.Fields3
    AND adddate('Date Value', A.Fields4) = B.Fields4
WHERE B.Fields1 IS NULL

A simple left join to return rows that are not found in table b


TG

[daveshack]

Hi techgnome. Thanks for quick reply. I had the feeling it was something simple like that.

Just a quick question from a newbie, why the WHERE clause in the query you gave me

WHERE B.Fields1 IS NULL

I would have thought that all rows in B would have a Field1 that is NOT NULL (becuase it is a Primary Key and cannot be Null). I am at work replying here, so I cant test the query.

Thanks again for the reply, its a great help.

Dave