How to write a join

[mkt2]

I am using an access database. I have a table name strat_data_call which has the fields rec_no, agency, due_dt and a table named ccd_data_call with fields strat_rec_no, user_id. When an action takes place against a particular record in the strat_data_call a record of that action is inserted into the ccd_data_call table with the rec_no field inserted into the strat_rec_no field and the cookie.user_id is insertered into the user_id.

I need to list all the records from the strat_data_call table which do not have corresponding records that were placed in the ccd_data_call table by the user who is signed on (cookie.user_id). The rec_no can be in the ccd_data_call table numerious times but can only be in there once for any particular user.

How would I write the sql to display this. I have tried several different joins but none work.
Thanks

[Swift · Accepted solution]

mkt,

Try:

Select *
From strat_data_call s
Left Outer Join ccd_data_call c
On s.rec_no = c.strat_rec_no
Where c.user_id Is Null

Swift

[mkt2]

That is almost there. The only problem is the "null" logic. There will always be a user_id in the ccd_data_call table field. But it will depend on who put the record there. So, I need the records in the strat_data_call to display even if they find a match of the rec_no and no match for the user_id that is logged on.

[paross1]

entered in error