• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

problem with Sql query

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
damixa
Asked:
damixa
  • 2
  • 2
1 Solution
 
chaauCommented:
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
 
chaauCommented:
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
 
damixaAuthor Commented:
Amazing, thanks
0
 
damixaAuthor Commented:
Much appreciated
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now