Help on replacing sub-query with JOIN

[Michael Flanagan]

I have a question about replacing a sub-query with a JOIN.
First, is this the appropriate forum? If not, where should I post?

I have two tables, CO and CLIP:

table CO:
Id
--
2
4
7

table CLIP:
Id Cust_num
-- --------
2 12
2 24
7 24

I want to SELECT all CO records for which there is no corresponding record in the CLIP table with Cust_num 12. That should be CO records with an Id of 4 and 7 (neither of which exist in CLIP with a Cust_num of 12; Id 4 doesn't exist in CLIP at all, and 7 exists, but not with a Cust_num of 12).

Using a sub-query I can say:

SELECT CO.Id
WHERE CO.Id NOT IN
(SELECT Id from CLIP
WHERE Cust_num = 12)

That should return me two CO records, Id 4 and 7.

Now I'd like to do the same using JOINs (because VIEWs don't allow sub-queries). If I just wanted to find those CLIP records that didn't have a matching Id in the CO table, I could say something like:

SELECT Id
FROM CO LEFT OUTER JOIN CLIP
ON (CO.Id = CLIP.Id)
WHERE CLIP.Id IS NULL

That should return me the single CO record with the Id of 4.

But I want to get all those CO records that don't have a match in CLIP for Cust_num 12.

Any ideas on doing that? I'm not sure it's possible.

Thanks in avance.