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

General SQL Server Forums

New to SQL Server Programming

Join

Author

Topic

chrisnorris007
Starting Member

18 Posts

Posted - 2014-09-19 : 14:39:36

I am trying to join two tables together using a particular field.

the code to reproduce is:


declare @chris as table
(
chrisid int identity (1,1) primary key,
name varchar(100),
SrvStopID int
)

declare @neal as table
(
chrisid int identity (1,1) primary key,
name varchar(100),
SrvStopID int
)

insert into @Chris
	select 'John',1
	union all
	select 'Jacob',1
	union all
	select 'Jinglehiemer',1
	union all
	select 'Smith',1

insert into @Neal
	select 'Neal',1
	union all
	select 'SmithSon',1
	union all
	select 'Jones',2

select * from @Neal N inner join
	@Chris C on C.SrvStopID=N.SrvStopID

my result is:
chrisid name SrvStopID chrisid name SrvStopID
1 Neal 1 1 John 1
1 Neal 1 2 Jacob 1
1 Neal 1 3 Jinglehiemer 1
1 Neal 1 4 Smith 1
2 SmithSon 1 1 John 1
2 SmithSon 1 2 Jacob 1
2 SmithSon 1 3 Jinglehiemer 1
2 SmithSon 1 4 Smith 1

and my result should be: Just the results in @Neal that match SrvStopID in @Chris

Please help me! I have no idea what I am doing wrong

Thanks so much,
Chris

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-19 : 15:16:55

That's what your're getting! All the rows in @Chris have SrvStopID = 1. Two rows in @neal have the same value. So the join is 4*2 = 8 rows as shown.

chrisnorris007
Starting Member

18 Posts

Posted - 2014-09-19 : 15:28:34

I realized what happened. I should have used a cursor to iterate through each unique srvstop and add records..its fixed now..thanks for looking though

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-19 : 15:33:33

NO! NO! NO!

you don't want to use a cursor. There is no need for it and it will just slow everything down.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources