[SOLVED] Multi-table SQL question

[tarsier]

I have a simple (in principle) query I am having a hard time getting the working.

I want to select a subset from table1 that does not appear in a subset of another table2

 

table1

--index (primany)

--part_num

--serial_num

 

table2

--index(primary)

--part_num

--file_id

 

I want to select everything from table1 where

  table1.serial_num='1234'  AND

  table1.part_num does not match table2.part_num in the subset of table2 where file_id is not null

 

this is to say that there is a subset in table2 of part_num's that have non-NULL file_id's. I want a list of all table1.part_num's that are not part of this table2 subset and have a given serial number.

 

Does this make sense? Any help would be great.

 

- Dave

 

 

[Barand]

try

 

SELECT t1.* 
FROM table1 t1
LEFT JOIN table2 t2 ON  t1.part_num = t2.part.num AND t2.file_id IS NOT NULL
WHERE t1.serial_num = '1234'
AND t2.part_num IS NULL

[tarsier]

Thanks. That did it.