getting duplicate records in result

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)

getting duplicate records in result

Author

Topic

BitShift
Yak Posting Veteran

98 Posts

Posted - 2006-09-27 : 16:49:21

stored procedure, with paramater @fparentid

TABLE A
--------
id
name
fdata

TABLE B
--------
fid
cfid

where tableb.fid is tablea.id

the sql below gives be a resultset that has duplicates. its alomst as if the join is happening twice ?

SELECT a.name,b.fid,b.cfid AS cform_id
FROM b
JOIN a
ON a.id = b.cfid AND b.fid = @fParentid
ORDER BY b.fid

My resultset lists the correct items, but the resultset has a duplicate result. For example, on one test i get 16 records, when I should only get 8. The last 8 are an exact copy of the 1st 8.

What am i doing wrong ?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-27 : 17:00:43

Sounds like there could be a problem with your data. Post the data for the example you gave by running these two queries (replace ? with the value you passed to the sp when you got the 16 rows.


SELECT id, name FROM a WHERE id IN (SELECT cfid FROM b WHERE fid = ?)
SELECT fid, cfid FROM b WHERE fid = ?

BitShift
Yak Posting Veteran

98 Posts

Posted - 2006-09-27 : 17:28:52

quote:
Originally posted by snSQL

Sounds like there could be a problem with your data. Post the data for the example you gave by running these two queries (replace ? with the value you passed to the sp when you got the 16 rows.

You beat me to it. I had been beating my head for the last 2 hrs looking at my join, trying to figure out why I was getting duplicates...when in fact THERE WAS DUPLICATES in the data

problem resolved

BitShift
Yak Posting Veteran

98 Posts

Posted - 2006-09-27 : 17:29:12

remove this post

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-27 : 21:06:02

quote:
Originally posted by BitShift

remove this post

You can delete that reply

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-27 : 22:19:43

Darn, I was going to suggest you were joining on the wrong column.
Please remove this post.


where tableb.fid is tablea.id
,,,
SELECT a.name,b.fid,b.cfid AS cform_id
FROM b
JOIN a
ON a.id = b.cfid

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources