wrong result in join

Question

wrong result in join

msh083

SSC Journeyman

Points: 80
More actions
February 3, 2016 at 3:35 am

#324474

i have a view:
SELECT id_reg, name_reg, id_card, name_card, SUM(Expr1) AS Expr1
FROM dbo.d1
GROUP BY id_reg, name_reg, id_card, name_card
if if add a another view:
SELECT dbo.d1.id_reg, dbo.d1.name_reg, dbo.d1.id_card, dbo.d1.name_card, SUM(dbo.d1.Expr1) AS Expr1, dbo.d2.Expr1 AS Expr2
FROM dbo.d1 INNER JOIN
dbo.d2 ON dbo.d1.id_card = dbo.d2.id_card
GROUP BY dbo.d1.id_reg, dbo.d1.name_reg, dbo.d1.id_card, dbo.d1.name_card, dbo.d2.Expr1
result of SUM(Expr1) AS Expr1 is wrong!
how i do?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

Please post CREATE TABLE statements for the tables involved (including constraints and indexes), INSERT statements with some sample data to illustrate the problem, the incorrect output you get now, and the expected output along with an explanation of the logic behind it.

Your current post is like calling the doctor and saying "I've got a pain somewhere, what pill should I take?" I can give you a generic painkiller, but without proper diagnosis I run the risk that it will not help or even make your problems worse.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

msh083 (2/3/2016)
i have a view:
SELECT id_reg, name_reg, id_card, name_card, SUM(Expr1) AS Expr1
FROM dbo.d1
GROUP BY id_reg, name_reg, id_card, name_card
if if add a another view:
SELECT dbo.d1.id_reg, dbo.d1.name_reg, dbo.d1.id_card, dbo.d1.name_card, SUM(dbo.d1.Expr1) AS Expr1, dbo.d2.Expr1 AS Expr2
FROM dbo.d1 INNER JOIN
dbo.d2 ON dbo.d1.id_card = dbo.d2.id_card
GROUP BY dbo.d1.id_reg, dbo.d1.name_reg, dbo.d1.id_card, dbo.d1.name_card, dbo.d2.Expr1
result of SUM(Expr1) AS Expr1 is wrong!
how i do?

a) dbo.d2 doesn't contain all of the id_card values in dbo.d1

b) id_card is not unique in one or both tables

c) a and b

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Posting the same problem in multiple places just causes confusion.

Thread should probably continue at http://www.sqlservercentral.com/Forums/Topic1757984-392-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass