Join Question - 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)

Join Question

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-12-05 : 13:16:33

Somewhat new to joins, and have a simple question.

Have the following:

select * from POS where CLIENT_CODE = '993013002' - 121 RESULTS
select * from PREVPOS where CLIENT_CODE = '993013002' - 122 RESULTS

I want to join on the CLIENT_CODE and have a result set of 122, I have tried the following, and am not getting the desired results:

This is what im doing:

select pr.CLIENT_CODE, pr.OPEN_POSITION, p.OPEN_POSITION
from POS p
left outer join
PREVPOS pr on pr.CLIENT_CODE = p.CLIENT_CODE
where pr.client_code = '993013002'

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-05 : 14:20:04

The table that you want all the rows from must go on the left of a left outer join

select pr.CLIENT_CODE, pr.OPEN_POSITION, p.OPEN_POSITION
from PREVPOS pr
left outer join POS p on pr.CLIENT_CODE = p.CLIENT_CODE
where pr.client_code = '993013002'

or you could use a right outer join

select pr.CLIENT_CODE, pr.OPEN_POSITION, p.OPEN_POSITION
from POS p
right outer join PREVPOS pr on pr.CLIENT_CODE = p.CLIENT_CODE
where pr.client_code = '993013002'

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-12-05 : 14:22:48

Shall I assume if I get over 14,762 records back, I need to join on more than one row?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-05 : 14:34:28

No - sorry I just realized, you mustn't join on CLIENT_CODE. What is the primary key (unique row identifier) column in the POS and PREVPOS tables? You must join on that column.

The way you have it now every row from PREVPOS is joined with every row from POS (so 121 * 122 = 14762)

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-12-05 : 15:18:06

what if neither table has a primary key? all they have are indexes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 15:22:34

quote:
Originally posted by duhaas

select pr.CLIENT_CODE, pr.OPEN_POSITION, p.OPEN_POSITION
from POS p
left outer join
PREVPOS pr on pr.CLIENT_CODE = p.CLIENT_CODE
where pr.client_code = '993013002'

select pr.CLIENT_CODE, pr.OPEN_POSITION, p.OPEN_POSITION
from POS p
left outer join
PREVPOS pr on pr.CLIENT_CODE = p.CLIENT_CODE
where p.client_code = '993013002'

or

select pr.CLIENT_CODE, pr.OPEN_POSITION, p.OPEN_POSITION
from POS p
left outer join
PREVPOS pr on pr.CLIENT_CODE = p.CLIENT_CODE AND pr.client_code = '993013002'

Peter Larsson
Helsingborg, Sweden

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-12-05 : 15:25:03

have the smae issue where we get the 14,000 + results

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 15:42:06

Have you tried DISTINCT after the SELECT but before pr.CLIENT_CODE? -< SELECT DISTINCT pr.CLIENT_CODE ...
It seems that one of the tables holds a lot of duplicates.

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-05 : 16:07:14

quote:
Originally posted by duhaas

what if neither table has a primary key? all they have are indexes

Primary key, unique index - do you have a way to tell two rows in POS or PREVPOS from each other, you said you have 121 rows on POS that match the given CLIENT_CODE and 122 rows on PREVPOS that match the given CLIENT_CODE and you want to join them. But what is your join criteria, what is the basis for taking row x from POS and matching it to row y in PREVPOS. It can't be CLIENT_CODE because that matches the first row in POS to 122 rows in PREVPOS and the second row in POS to 122 rows in PREVPOS and ... 14762 rows later!

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-12-05 : 16:17:24

Well, i guess to answer the question, if I use what the index which is the same on both tables (sectype, ticker, buysell, clientcode) and use them in the join, does that solve my issue?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 16:23:51

Most probably yes.

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-05 : 16:28:10

quote:
Originally posted by duhaas

Well, i guess to answer the question, if I use what the index which is the same on both tables (sectype, ticker, buysell, clientcode) and use them in the join, does that solve my issue?

As long as it is a unique index.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources