Table Join - SQL Server Forums

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)

Table Join

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-19 : 09:43:09

I need to know how to join another table to this statement.
The table (tempDep) will be used to verify that a monetary transaction (trancode = 'ACH') has occured. If it has, it would be selected as a record to insert into the tempQXMT table. The following statement selects all of the records from the recordset that meet the criteria of accptcode = 'A'.

**********************************************************************
--create a the tempQXMT table
select t.DLRCODE, t.acctnum, t.tranamt, t.refacct, t.trancode, t.trandte
into tempQXMT
from tempXMT T left outer join
tempqik Q on
t.DLRCODE = q.NTSRSN
where q.DLRCODE is not null and q.accptcode = 'A'
create index IDX_Acctnum on tempQXMT (acctnum)

--CHANGE THE COL NAME NTSRSN TO DLRCODE
UPDATE TEMPQXMT SET DLRCODE = LEFT(DLRCODE,3)
create index IDX_DLRCODE on tempQXMT (dlrcode)
**********************************************************************

If you need more info to help me, let me know!

Thanks!
Teresa

"Happiness is found along the way;
not at the end of the road."

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-19 : 13:13:17

I finally found some documentation on a Star-Shaped Multi-Table Join. I've come up with this code, but instead of pulling all the data for specific dealer, it's not choosing any. If you have any ideas, please let me know.

Thanks!
Teresa
*********************************************************************
select t.DLRCODE, t.acctnum, t.tranamt, t.refacct, t.trancode, t.trandte
into tempQXMT2
from tempXMT T left outer join
tempqik Q on
t.DLRCODE = q.NTSRSN
left outer join tempdep as d on
q.ntsrsn = d.ntsrsn
where q.DLRCODE is not null and q.accptcode = 'A' and d.trancode = 'ACH'
create index IDX_Acctnum on tempQXMT2 (acctnum)
*********************************************************************

"Happiness is found along the way;
not at the end of the road."

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-19 : 13:21:24

when you write outer joins, bear in mind that the scope of the 'where' clause is the entire rowset specified in the 'from' clause, not individual rowspace scopes ( before any joins ).

the rowspace you've aliased as 'q' below will contain nulls when outer joined to the other tables, but you will eliminate those rows by specifying "q.accptcode = 'A'" in the where.

moving it into the 'on' clause should do what you want.

setBasedIsTheTruepath
<O>

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-19 : 13:24:49

quote:
"q.accptcode = 'A'"
moving it into the 'on' clause should do what you want.

Which on clause? Do I need parens?

Thanks for your help!

"Happiness is found along the way;
not at the end of the road."

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-19 : 13:31:27

*UGH* TYPOS got me!!! This code works now!

select t.DLRCODE, t.acctnum, t.tranamt, t.refacct, t.trancode, t.trandte
into tempRXMT2
from tempXMT T left outer join
tempRal r on
t.DLRCODE = r.NTSRSN
left outer join tempdep as d on
r.ntsrsn = d.ntsrsn
where r.DLRCODE is not null and r.accptcode = 'A' and d.trancode = 'ACH'
create index IDX_Acctnum on tempRXMT2 (acctnum)
UPDATE TEMPrXMT2 SET DLRCODE = LEFT(DLRCODE,3)

Thanks for holding my hand!

"Happiness is found along the way;
not at the end of the road."

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-19 : 13:33:11

the on clause immediately following the 'left outer join
tempqik Q'. don't think you need parens, quick to check either way.

setBasedIsTheTruepath
<O>

Jay99

468 Posts

Posted - 2002-03-19 : 14:22:33


select
    t.DLRCODE,
    t.acctnum,
    t.tranamt,
    t.refacct,
    t.trancode,
    t.trandte
into tempRXMT2
from
    tempXMT T
    inner join tempRal r
        on t.DLRCODE = r.NTSRSN
    inner join tempdep d
        on r.ntsrsn = d.ntsrsn
where
    r.DLRCODE is not null and
    r.accptcode = 'A' and 
    d.trancode = 'ACH'

Once you put constraints on fields in your 'right side' tables of a left join, you are essentailly doing an inner join. Take a look at the execution plans . . . they are the same.

Jay
<O>

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-19 : 15:29:40

quote:

Once you put constraints on fields in your 'right side' tables of a left join, you are essentailly doing an inner join. Take a look at the execution plans . . . they are the same.

Thank you for help Jay! That code works too! :o)

"Happiness is found along the way;
not at the end of the road."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources