Solved

Help with query, dup results showing

Posted on 2014-10-20
24
126 Views
Last Modified: 2014-10-20
I need help with the following query.

The first two display all results from two separate tables, the third query is a join but displays duplicate results, how can I prevent this from happening ?

Below are the three queries and attached a screenshot of the results.

---- first table (BillPaymntsRecvd) -----


SELECT  a.PaymntId ,
        a.CaseId ,
        a.BillsMainId ,
        a.ItemId ,
        a.ItemDesc ,
        a.PmtRecd ,
        a.Balance
FROM    dbo.BillPaymntsRecvd a
WHERE caseid = 11064
ORDER BY a.PaymntId DESC

---- second table (BillPaymentsRecvdTA) -----


SELECT  a.PaymntidTA ,
        a.Caseid ,
        a.BillsMainid ,
        a.ItemId ,
        a.ItemDesc ,
        a.PmtRecd ,
        a.Balance
FROM    dbo.BillPaymentsRecvdTA a
WHERE caseid = 11064
ORDER BY a.PaymntIdTA DESC

--- Query with JOIN and duplicates ---

SELECT  a.PaymntId ,
        a.CaseId ,
        a.BillsMainId ,
        a.ItemId ,
        a.ItemDesc ,
        a.PmtRecd ,
        a.Balance
FROM    dbo.BillPaymntsRecvd a
        INNER JOIN dbo.BillPaymentsRecvdTA AS b ON a.ItemId = b.ItemId
WHERE   a.caseid = 11064
        AND a.Balance <> 0
        ORDER BY a.PaymntId DESC
0
Comment
Question by:amucinobluedot
  • 12
  • 8
  • 3
  • +1
24 Comments
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 317 total points
ID: 40392041
If the relationship from both tables aren't 1 to 1 (one to one) then you'll have duplicate records.
Depending on the database model you may resolve this issue with the DISTINCT keyword:
SELECT  DISTINCT a.PaymntId ,
         a.CaseId ,
         a.BillsMainId ,
         a.ItemId ,
         a.ItemDesc ,
         a.PmtRecd ,
         a.Balance 
FROM    dbo.BillPaymntsRecvd a
      INNER JOIN dbo.BillPaymentsRecvdTA AS b ON a.ItemId = b.ItemId
WHERE   a.caseid = 11064 AND a.Balance <> 0
ORDER BY a.PaymntId DESC 

Open in new window


If this doesn't resolve the issue please provide samples of data for all three queries.
0
 
LVL 7

Assisted Solution

by:slubek
slubek earned 46 total points
ID: 40392043
You don't use any values from dbo.BillPaymentsRecvdTA AS b, so in my opinion it is not necessery to use JOIN in that query.
If you have multiple values of dbo.BillPaymentsRecvdTA.ItemID which have to be joined with caseid=11064, you get multiple records from dbo.BillPaymntsRecvd.
So:
1. remove Join
2. Use

group by  a.PaymntId ,
        a.CaseId ,
        a.BillsMainId ,
        a.ItemId ,
        a.ItemDesc ,
        a.PmtRecd ,
        a.Balance 

Open in new window


clause.
0
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 137 total points
ID: 40392052
If there are duplicates appearing then it is highly likely that there are either multiple records in the two tables with the same ItemID. Can you verify that you are using the correct field to join the two tables? Just a quick glance at the first two queries, it would appear that either PaymntID, BillsMainID or CaseID should be also in the join statement.
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

by:amucinobluedot
ID: 40392056
I may have pasted the wrong query. I AM using the b.name

If I used DISTINCT I get no results. Where only the exact same should not be duplicated.

----

SELECT  a.PaymntId ,
        a.CaseId ,
        a.BillsMainId ,
        a.EntryBy ,
        a.ItemId ,
        a.ItemDesc ,
        a.ItemLongDec ,
        a.UnitCost ,
        a.Qty ,
        a.PmtRecd ,
        a.Balance ,
        a.apymntdate ,
        a.firmid ,
        b.PaymntidTA ,
        b.ItemId AS ItemidTA
FROM    dbo.BillPaymntsRecvd a
        INNER JOIN dbo.BillPaymentsRecvdTA AS b ON a.ItemId = b.ItemId
WHERE   a.BillsMainId = 1416
        AND a.Balance <> 0
0
 
LVL 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 317 total points
ID: 40392065
Can you provide sample data?
0
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 137 total points
ID: 40392075
If you are filtering on a.BillsMainID = 1416, and BillsMainID is also in BillPaymentsRecvdTA, would you not either need to use that field in your join statement or filter on that field in BillPaymentsRecvdTA as well?
0
 

Author Comment

by:amucinobluedot
ID: 40392101
I am trying to display payments, its the same payment in two tables, I won't get into the specifics of why I am doing this but we do need to enter the same payment in both tables, the ID's are different though, but I only want to display the payment from one of the tables BUT I need to pass on to the next page both "ID's" because the next page will update the values in both tables.

So .. how do I JOIN the tables so I can have both ID's while displaying the payment information only once ?  So that if the ItemID is the same on both tables it should display it only once regardless of  'paymntidTA' being different.

----

This are the correct queries:

SELECT  a.PaymntId ,
        a.CaseId ,
        a.BillsMainId ,
        a.ItemId ,
        a.ItemDesc ,
        a.PmtRecd ,
        a.Balance
FROM    dbo.BillPaymntsRecvd a
WHERE a.BillsMainId = 1423
ORDER BY a.PaymntId DESC

SELECT  a.PaymntidTA ,
        a.Caseid ,
        a.BillsMainid ,
        a.ItemId ,
        a.ItemDesc ,
        a.PmtRecd ,
        a.Balance
FROM    dbo.BillPaymentsRecvdTA a
WHERE a.BillsMainId = 1423
ORDER BY a.PaymntIdTA DESC

SELECT  
            a.PaymntId ,
        a.CaseId ,
        a.BillsMainId ,
        a.EntryBy ,
        a.ItemId ,
        a.ItemDesc ,
        a.ItemLongDec ,
        a.UnitCost ,
        a.Qty ,
        a.PmtRecd ,
        a.Balance ,
        a.apymntdate ,
        a.firmid ,
        b.PaymntidTA ,
        b.ItemId AS ItemidTA
FROM    dbo.BillPaymntsRecvd a
        INNER JOIN dbo.BillPaymentsRecvdTA AS b ON a.ItemId = b.ItemId
WHERE   a.BillsMainId = 1423
        AND a.Balance <> 0

--- The third one displays a duplicate because it displays the entry from each table.
results.gif
0
 

Author Comment

by:amucinobluedot
ID: 40392105
This is the results from all 3 queries
All-results.gif
0
 
LVL 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 317 total points
ID: 40392113
This is the results from all 3 queries
I'm wondering why is missing one record in the 2nd query (PaymntidTA = 207)
0
 

Author Comment

by:amucinobluedot
ID: 40392125
It should be one result from table BillPaymntsRecvd

It should be one result from table PaymntIdTA

Is there a way to have the join so that I have both "ID's" in the resulting join but only ONE result ?

Something like:

Paymntid, PaymntidTA, ... the rest of the entries which are identical in both tables

One record with both ID's instead of two records.
0
 
LVL 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 317 total points
ID: 40392127
Anyway, the only solution for returning a single row it's to have PaymntidTA separated by commas or another separator character (207, 209).
It's that an acceptable solution for you?
0
 

Author Comment

by:amucinobluedot
ID: 40392147
Actually ... it should only display the latest PaymntidTA .. can we try that instead ?  
Only the 209 and not the 207 ... this might be the issue here, its taking also the value of a previous paymentidTA and also displaying it
0
 
LVL 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 317 total points
ID: 40392155
That's what I was wondering about. Why 207 it's returned if it isn't on the 2nd query?
0
 

Author Comment

by:amucinobluedot
ID: 40392161
Correct, it should not be there, sounds like that is the issue, can we filter it so that it only shows the last (top) entry of the PaymntidTA ?
0
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 137 total points
ID: 40392162
Tacking on to Vitor's question, in the 3rd query, include the BillMainID and Case ID from table B. It would seem that these will not match the values you are expecting.
0
 

Author Comment

by:amucinobluedot
ID: 40392175
I want to display the third query but only the result where the PaymntidTA is the last entry (highest value)
0
 
LVL 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 317 total points
ID: 40392176
Try this query:
SELECT  a.PaymntId ,
         a.CaseId ,
         a.BillsMainId ,
         a.EntryBy ,
         a.ItemId ,
         a.ItemDesc ,
         a.ItemLongDec ,
         a.UnitCost ,
         a.Qty ,
         a.PmtRecd ,
         a.Balance ,
         a.apymntdate ,
         a.firmid ,
         b.PaymntidTA,
         b.ItemId AS ItemidTA
 FROM    dbo.BillPaymntsRecvd a
    INNER JOIN dbo.BillPaymentsRecvdTA AS b ON a.ItemId = b.ItemId
 WHERE   a.BillsMainId = 1416
  AND a.Balance <> 0 
  AND b.PaymntidTA = (SELECT TOP 1 b.PaymntidTA 
    					FROM	BillPaymentsRecvdTA  ta
    					WHERE	a.ItemId = ta.ItemId
    					ORDER BY ta.PaymntidTA DESC) 

Open in new window

0
 

Author Comment

by:amucinobluedot
ID: 40392194
Did not return anything :$
0
 

Author Comment

by:amucinobluedot
ID: 40392196
Never mind, I was using the wrong ID, it in fact still returned 2 records.
0
 

Author Comment

by:amucinobluedot
ID: 40392197
Screenshot
qry.gif
0
 
LVL 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 317 total points
ID: 40392198
Both records has the same PaymntidTA value?
If so you need to add the DISTINCT keyword.
0
 

Author Comment

by:amucinobluedot
ID: 40392199
That's the whole thing, the ID is different. I need to display the result that has the latest entry (Highest ID)
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40392239
That's weird. Something must be missing. Please run the following two queries and tell me what returns, ok?
SELECT  TOP 1 b.PaymntidTA,
 FROM    dbo.BillPaymntsRecvd a
    INNER JOIN dbo.BillPaymentsRecvdTA AS b ON a.ItemId = b.ItemId
 WHERE   a.BillsMainId = 1416 AND a.Balance <> 0 
ORDER BY b.PaymntidTA DESC


SELECT  MAX(b.PaymntidTA)
 FROM    dbo.BillPaymntsRecvd a
    INNER JOIN dbo.BillPaymentsRecvdTA AS b ON a.ItemId = b.ItemId
 WHERE   a.BillsMainId = 1416   AND a.Balance <> 0 

Open in new window

0
 

Author Comment

by:amucinobluedot
ID: 40392249
I am going to close this and take a different approach. The above gives me two results. I will put the latest ID in a session and filter it that way see if that works.

Thanks !
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL GROUP BY 6 75
Access table not showing correct column 6 20
Error in sql query statment. 21 44
Get data from two MySQL tables 6 22
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

756 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