Table A
Table B

Both table A and table B have a field: fileId

I want to select the fileId’s that are in table B, but not if they are also in the following SELECT result set from table A

SELECT fileId FROM a WHERE filetype=‘jpg’;

I hope you understand… I have only just grasped the concepts of joins but I’m at a loss as to how to do this?… Any help would be much appreciated…

Alternatively is there a way to create a new field on the fly so that when I am looping through the second sql result set there is field that says fileId is in both table B and also in the tableA sql result set….

kind regards J

Please provide table structure and example data so we have something to work with. I see what you want to do, and in that case JOIN won't do you any good.

Thnaks Darek: this is as simple as i can make it... (plus it makes it easier for me to understand!)...

Table A:

fileId (primary index) - bignum
fileType - var
fileName - var


Table B:

fileId - bignum
projectId - bignum

fileId+projectId (unique index)

I want to select all the fileIds in A that are not in the following sql result set:
SELECT fileId FROM B WHERE projectId=‘1’;

Thanks to Subdee from another forum for that...

SELECT b.fileId FROM tableB b
WHERE b.fileId NOT IN
(SELECT a.fileId FROM tableA a WHERE filetype='jpg')
AND filetype='jpg'

Thanks r937, i will take a look at that... it looks genius!...
kind regards Jonathan