Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

problem with Sql query

Posted on 2013-06-24
4
Medium Priority
?
289 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 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

824 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