Join Issue

[Moxy1]

I have been having problems with the following:

select a_no,a_id_no
from tbl.account, tbl.state
where s_no = a_no
and a_agent_code = 'RET'
and b_state = ‘NY’
/


I'm trying to get all records from tbl.account even if there isn't a match in tbl.state

I have tried, s_no = a_no (+)
I have also tried, a_no *= s_no

When I tried right outer join tbl_state on s_no = a_no I get an error, "SQL command not properly ended".

any ideas?

Thanks,
Moxy

 

[swampBoogie]

select a_no,a_id_no
from tbl.account left join tbl.state
on s_no = a_no
where a_agent_code = 'RET'
and b_state = ‘NY’

 

[nigelrivett]

Is b_state on tbl.state?
If so the
and b_state = ‘NY’
will turn that into an inner join as
and null = ‘NY’
will never be true.


select a_no,a_id_no
from tbl.account
left join tbl.state
on s_no = a_no
and b_state = ‘NY’
where a_agent_code = 'RET'


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.