How to write a join

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mkt - 28 Jun 2007 17:51 GMT

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

Reply to this Message

Swift - 28 Jun 2007 19:57 GMT

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

Reply to this Message

mkt - 28 Jun 2007 20:11 GMT

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.

Reply to this Message

paross1 - 28 Jun 2007 20:37 GMT

Then reverse the tables in your query and select from ccd_data_call first then
OUTER JOIN to the strat_data_call table.

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

Phil

Reply to this Message

Webmaster Forum / ColdFusion / Advanced Techniques / June 2007

How to write a join