question on joins...

Question

question on joins...

Christopher Klein

SSCrazy

Points: 2648
More actions
May 3, 2007 at 1:13 pm

#177789

(EDIT: lost half the msg )
Hi there,
I have a query that I am pulling 5 columns of data, 3 from one table, 2 from another.
SELECT
ECE.COL1,
ECE.EOM_DATE,
ECE.COL3,
COALESCE(gVA.IsPrior,0) as [IsPrior],
COALESCE(gVA.IsPrime,1) as [IsPrime]
FROM
HOLDING ECE
JOIN VALIDACCT gVA
ON gVA.AccountCode=ECE.PORTFOLIO AND gVA.[curryear]=YEAR(ECE.EOM_DATE) AND gVA.[currmonth]=MONTH(ECE.EOM_DATE)
VALIDACCT is a typically non-related table however it has two columns that I care about.
The data is something like;accountcode,curryear,currmonth,isprior,isprime
ACCT1    2007    01     0    0
ACCT1    2007    02     1    0
ACCT1    2007    03     2    3
ACCT1    2007    04    NULL 1
The only way to match is to pull apart EOM_DATE and match on year and month.
the HOLDING table has about 5000 records in it and VALIDACCT has about 2000. Why am I getting more than 5000 returns when there is only 1 match for each item in HOLDING?
Normally im not so brain dead on joins, but what am I missing?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Change your join from JOIN to INNER JOIN.

JOIN by itself is a LEFT JOIN which pulls all records from the left table (HOLDING in your case) and only the records from the right table (VALIDACCT) that match.

An INNER JOIN will only return the records that exist in BOTH the left and right tables.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Are you sure about this one?

"JOIN by itself is a LEFT JOIN which pulls all records from the left table (HOLDING in your case) and only the records from the right table (VALIDACCT) that match."

DECLARE @a table (id int not null)

DECLARE @b-2 table (id int not null)

insert into @a (id) values (1)

insert into @a (id) values (2)

insert into @a (id) values (3)

insert into @b-2 (id) values (2)

insert into @b-2 (id) values (3)

insert into @b-2 (id) values (4)

Select * from @a A join @b-2 B on A.id = B.id

Select * from @a A left outer join @b-2 B on A.id = B.id

Select * from @a A inner join @b-2 B on A.id = B.id

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

OK, I now have egg on my face! 🙁 I was making an assumption, and we all know what that does.

I should have tested this before opening my BIG mouth!!!

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Well most of what you said was accurate .

Thanks for helping out.

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Try create unique constraint on VALIDACCT ([curryear], [currmonth])

See if you really have single record per item.

_____________
Code for TallyGenerator