Solved

problem with Sql query

Posted on 2013-06-24
4
259 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
  • 2
  • 2
4 Comments
 
LVL 24

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 24

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now