Solved

problem with Sql query

Posted on 2013-06-24
4
273 Views
Last Modified: 2013-06-24
Im trying to update a field in the table where I'm joining the table to an aggregation of itself. The problem is that im getting a cartesian product, rather than an Inner join.

can somebody point me to the right direction?

UPDATE dbo.tblBNKRecon
SET dbo.tblBNKRecon.GroupedPmtID = GRPPMTID
From (SELECT tblBNKRecon.GroupedPmtID, tblBNKRecon.BAITypeCode, tblBNKRecon.Status, tblBNKRecon.NewDesc, tblBNKRecon.ID, tblBNKRecon.BankAccount, tblBNKRecon.BankDate
FROM tblBNKRecon INNER JOIN tblBNK_GRP_ENT ON tblBNKRecon.BankAccount = tblBNK_GRP_ENT.ACCT
WHERE (((tblBNKRecon.BAITypeCode)='169') AND ((tblBNKRecon.Status)='LD') AND ((tblBNKRecon.NewDesc) Is Not Null) AND ((tblBNK_GRP_ENT.GroupedPmts)=1)))as a
inner join (
select MIN(ID) as minimalID, NewDesc, BankAccount, BankDate, 
COALESCE(CONVERT(VARCHAR, MONTH(GETDATE())), '') +
COALESCE(CONVERT(VARCHAR, DAY(GETDATE())), '')+
COALESCE(CONVERT(VARCHAR, MIN(ID)), '') as GRPPMTID

from  dbo.tblBNKRecon

group by NewDesc, BankAccount, BankDate) as b
on a.NewDesc = b.NewDesc and
a.BankAccount = b.BankAccount and 
a.BankDate = b.BankDate

Open in new window

0
Comment
Question by:damixa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39273154
All I can suggest here without having access to your data is that your JOIN on a.NewDesc = b.NewDesc and a.BankAccount = b.BankAccount and a.BankDate = b.BankDate is not producing the UNIQUE combination.

I suggest you do the following: Run these two queries separate:

SELECT tblBNKRecon.GroupedPmtID, tblBNKRecon.BAITypeCode, tblBNKRecon.Status, tblBNKRecon.NewDesc, tblBNKRecon.ID, tblBNKRecon.BankAccount, tblBNKRecon.BankDate
FROM tblBNKRecon INNER JOIN tblBNK_GRP_ENT ON tblBNKRecon.BankAccount = tblBNK_GRP_ENT.ACCT
WHERE (((tblBNKRecon.BAITypeCode)='169') AND ((tblBNKRecon.Status)='LD') AND ((tblBNKRecon.NewDesc) Is Not Null) AND ((tblBNK_GRP_ENT.GroupedPmts)=1))

Open in new window


select MIN(ID) as minimalID, NewDesc, BankAccount, BankDate, 
COALESCE(CONVERT(VARCHAR, MONTH(GETDATE())), '') +
COALESCE(CONVERT(VARCHAR, DAY(GETDATE())), '')+
COALESCE(CONVERT(VARCHAR, MIN(ID)), '') as GRPPMTID
from  dbo.tblBNKRecon
group by NewDesc, BankAccount, BankDate

Open in new window


Check first of all that the queries themselves are not returning duplicate entries. Check that the number of rows match (they may not match, depending on your data). If you have not many rows you will be able to compare the results side-by-side.

Then, you need to run this query and check that the cnt column equals 1:
SELECT NewDesc, BankAccount, BankDate, count(*) as cnt
FROM tblBNKRecon
GROUP BY NewDesc, BankAccount, BankDate

Open in new window


If it is not you will be updating duplicate entries with the same GroupedPmtID value (probably it is also fine, depending on your ).

If possible, can you upload some fake data to SQLFiddle, or Access database, so that we could check it
0
 
LVL 25

Accepted Solution

by:
chaau earned 500 total points
ID: 39273158
Forget all that I've said. I think I found the problem. You do not join tblBNKRecon to the two subqueries. So, just do this, and I think you'll be fine:

UPDATE dbo.tblBNKRecon
SET dbo.tblBNKRecon.GroupedPmtID = GRPPMTID
From dbo.tblBNKRecon INNER JOIN
(SELECT tblBNKRecon.GroupedPmtID, tblBNKRecon.BAITypeCode, tblBNKRecon.Status, tblBNKRecon.NewDesc, tblBNKRecon.ID, tblBNKRecon.BankAccount, tblBNKRecon.BankDate
FROM tblBNKRecon INNER JOIN tblBNK_GRP_ENT ON tblBNKRecon.BankAccount = tblBNK_GRP_ENT.ACCT
WHERE (((tblBNKRecon.BAITypeCode)='169') AND ((tblBNKRecon.Status)='LD') AND ((tblBNKRecon.NewDesc) Is Not Null) AND ((tblBNK_GRP_ENT.GroupedPmts)=1)))as a
ON a.NewDesc = dbo.tblBNKRecon.NewDesc and
a.BankAccount = dbo.tblBNKRecon.BankAccount and 
a.BankDate = dbo.tblBNKRecon.BankDate
inner join (
select MIN(ID) as minimalID, NewDesc, BankAccount, BankDate, 
COALESCE(CONVERT(VARCHAR, MONTH(GETDATE())), '') +
COALESCE(CONVERT(VARCHAR, DAY(GETDATE())), '')+
COALESCE(CONVERT(VARCHAR, MIN(ID)), '') as GRPPMTID
from  dbo.tblBNKRecon
group by NewDesc, BankAccount, BankDate) as b
on a.NewDesc = b.NewDesc and
a.BankAccount = b.BankAccount and 
a.BankDate = b.BankDate

Open in new window

0
 

Author Comment

by:damixa
ID: 39273169
Amazing, thanks
0
 

Author Closing Comment

by:damixa
ID: 39273170
Much appreciated
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question