Incorrect Syntax with an Inner Join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Incorrect Syntax with an Inner Join

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2002-08-27 : 18:32:43

Hi everyone! Are you getting ready for the holiday?

I have two tables (Taxpayer & Banks). Each have a column called RTN. The banks table has two records. One with an rtn = 11111111, the second = 2222222. The taxpayer table has numerous records. The rtn column in this table can equal one of the two records in the banks table or neither of them. I'm trying to get the query to pull back the records that do NOT match. When I run the following code it returns records that do match as well as those that do not.

Can someone tell me where the error is? (SQL 7)


select taxpayer.rtn, taxpayer.dirstate, taxpayer.procstat, taxpayer.dan, taxpayer.lasmailbox 
from taxpayer
  inner join 
  (select b.rtn
   from banks B
   where b.rtn is not null)
  as B on taxpayer.rtn <> b.rtn
where (taxpayer.dirstate is not null and taxpayer.dirstate = 'FD') and taxpayer.accptcode = 'A'
       and (left(taxpayer.Dan,1) <> 'R' or left(taxpayer.dan,1) <> 'Q')

Thanks!
Teresa

Edited by - TJ on 08/27/2002 18:34:51

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-27 : 19:48:40

Teresa,

1) Yes, looking forward to it.

2) Why not use a LEFT JOIN ... WHERE B.RTN IS NULL? Something like:
select taxpayer.rtn, taxpayer.dirstate, taxpayer.procstat, taxpayer.dan, taxpayer.lasmailbox
from taxpayer
LEFT join banks B on taxpayer.rtn = b.rtn
where B.rtn IS NULL AND (taxpayer.dirstate is not null and taxpayer.dirstate = 'FD') and taxpayer.accptcode = 'A'
and (left(taxpayer.Dan,1) <> 'R' or left(taxpayer.dan,1) <> 'Q')

TJ
Posting Yak Master

201 Posts

Posted - 2002-08-28 : 09:40:58

quote:

Teresa,

1) Yes, looking forward to it.

2) Why not use a LEFT JOIN ... WHERE B.RTN IS NULL? Something like:
select taxpayer.rtn, taxpayer.dirstate, taxpayer.procstat, taxpayer.dan, taxpayer.lasmailbox
from taxpayer
LEFT join banks B on taxpayer.rtn = b.rtn
where B.rtn IS NULL AND (taxpayer.dirstate is not null and taxpayer.dirstate = 'FD') and taxpayer.accptcode = 'A'
and (left(taxpayer.Dan,1) <> 'R' or left(taxpayer.dan,1) <> 'Q')

I found the following code although I'm not sure what it's called or how it works without stating the 'join'. However, it does work. It looks like it's an 'implied' join, but not sure which one. If you have any insight on it, please let me know.


    update taxpayer
    set procstat = 'ZA', procdte = @dte
    where rtn not in (select rtn from banks) and procstat = '35' and
    (dirstate is not null and dirstate = 'FD') and accptcode = 'A'

Thanks for your help!
Teresa

Edited by - TJ on 08/28/2002 09:41:30

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-28 : 13:07:52

The clause where rtn not in (select rtn from banks) is not doing a JOIN, but rather a SUBQUERY in the WHERE clause. Using a subquery is another way of determining rows that don't match criteria. Generally, a JOIN works faster, but due to your limited number of rows, it might not be significant.

Edited by - ajarnmark on 08/28/2002 13:08:18

TJ
Posting Yak Master

201 Posts

Posted - 2002-08-28 : 14:02:24

quote:

The clause where rtn not in (select rtn from banks) is not doing a JOIN, but rather a SUBQUERY in the WHERE clause. Using a subquery is another way of determining rows that don't match criteria. Generally, a JOIN works faster, but due to your limited number of rows, it might not be significant.
Edited by - ajarnmark on 08/28/2002 13:08:18

Thanks for clarifying that! I'll keep the Join vs Subquery in mind when determining the quickest way to get the stored procedure to run.

I appreciate your help!

Teresa

Edited by - TJ on 08/28/2002 14:03:29

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources