to many record error - 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 2005 Forums

Transact-SQL (2005)

to many record error

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-01-15 : 13:04:44

Hi

I have 2 tables like this...

tbl_Error_Reports
ReportID = Int, counter

tbl_ErrorReportAssociation
ID Int
ReportID = Int, counter

Table holds 81 records which is correct, but if I join the 2 tables like this I get....

SELECT DISTINCT COUNT(dbo.tbl_ErrorReportAssociation.ReportID) AS Qty
FROM         dbo.tbl_ErrorReportAssociation INNER JOIN
                      dbo.tbl_Error_Reports ON dbo.tbl_ErrorReportAssociation.ReportID = dbo.tbl_Error_Reports.ReportID

I find this very weird, anyone know what might be the problem?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-15 : 13:15:42

What do you get when you run the query? May be there is 1-M relation between the Id and ReportID in tbl_ErrorReportAssociation table..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-01-15 : 13:21:41

"I get...." you get what?

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-01-15 : 13:31:31

Sorry, for that. I get 131 records.
The ReportID in tbl_Error_Reports is unique, but that ReportID occurs several times in the tbl_ErrorReportAssociation table.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-01-15 : 13:44:56

this query has DISTINCT COUNT, any reason why? it cant give you 131 records...whats the actual query you are using?

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-01-15 : 13:52:04

Hi

Even if I remove the distinct part, I still get Qty = 131. And the question is the one above.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-01-15 : 14:08:23

try it this way

SELECT  COUNT(DISTINCT dbo.tbl_ErrorReportAssociation.ReportID) AS Qty
FROM         dbo.tbl_ErrorReportAssociation INNER JOIN
      dbo.tbl_Error_Reports ON dbo.tbl_ErrorReportAssociation.ReportID = dbo.tbl_Error_Reports.ReportID

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-01-15 : 14:11:48

:-D
Yes that worked just fine, Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources