February 28, 2019 at 6:43 pm
The issue I am having here is when I run this query, it gives me duplicate rows as well in the result. I am using these 2 tables . TABLE Receivables has a Unique DOC NUMBER for each Customer ID. Note that Customer ID are Duplicate but Doc Number s are Unique.so that the combination of these 2 fields are not duplicate
In the same way, the 2nd Table RM20201 has also Unique Document Numbers with Customer Ids. I want to show all THOSE records from RECEIVABLE Table only whose Document Numbers (A.DOCNUMBR) are not present in TABLE RM20201, This query shows me the correct data but the result shows duplicate rows after running this query. Can you let me know why I am getting the correct data with duplicate rows?????? will appreciate your help
SELECT * FROM Receivables As A
JOIN RM20201 B
ON A.CUSTNMBR= B.CUSTNMBR
WHERE A.DOCNUMBR <> B.APTODCNM
AND A.DOCNUMBR <> B.APFRDCNM AND A.CURTRXAM <>0
March 1, 2019 at 2:40 am
ipervez784 - Thursday, February 28, 2019 6:43 PMThe issue I am having here is when I run this query, it gives me duplicate rows as well in the result. I am using these 2 tables . TABLE Receivables has a Unique DOC NUMBER for each Customer ID. Note that Customer ID are Duplicate but Doc Number s are Unique.so that the combination of these 2 fields are not duplicate
In the same way, the 2nd Table RM20201 has also Unique Document Numbers with Customer Ids. I want to show all THOSE records from RECEIVABLE Table only whose Document Numbers (A.DOCNUMBR) are not present in TABLE RM20201, This query shows me the correct data but the result shows duplicate rows after running this query. Can you let me know why I am getting the correct data with duplicate rows?????? will appreciate your helpSELECT * FROM Receivables As A
JOIN RM20201 B
ON A.CUSTNMBR= B.CUSTNMBR
WHERE A.DOCNUMBR <> B.APTODCNM
AND A.DOCNUMBR <> B.APFRDCNM AND A.CURTRXAM <>0
You already told the answer . Because you are joining by CUSTNMBR which contains duplicate records . It is better to try using except or left outer join to get the result .
SELECT * FROM Receivables As A
LEFT OUTER JOIN RM20201 B
ON A.DOCNUMBR = B.APTODCNM
WHERE A.CURTRXAM <>0 AND B.APTODCNM IS NULL
Saravanan
March 1, 2019 at 4:03 pm
Thank you so much Saravanan for your feedback. But I wanted those records which are mutually present in both tables. That's why I used Inner Join. but please let me know one thing as I am not clear when I joined two tables, by using Inner Join. DO I always use that field which has unique record in both tables or it can be that field , which has duplicate records in both table.
AS I mentioned in my previous post, that the combination of CUSTNO+DOCNO is unique in both tables, so can I join both tables by using these two fields, ? like
Select * from table1 join Table2
ON Table1.Custno=Table2.Custno
AND Table1.Doc No=Table2.DocNo
2nd, I am still not clear that why my SELECT query (Which I mentioned in previous post), was returning duplicate records. Can you please clear me one more time with an example.....Will appreciate your feedback
March 1, 2019 at 5:19 pm
ipervez784 - Friday, March 1, 2019 4:03 PMThank you so much Saravanan for your feedback. But I wanted those records which are mutually present in both tables. That's why I used Inner Join. but please let me know one thing as I am not clear when I joined two tables, by using Inner Join. DO I always use that field which has unique record in both tables or it can be that field , which has duplicate records in both table.
AS I mentioned in my previous post, that the combination of CUSTNO+DOCNO is unique in both tables, so can I join both tables by using these two fields, ? likeSelect * from table1 join Table2
ON Table1.Custno=Table2.Custno
AND Table1.Doc No=Table2.DocNo2nd, I am still not clear that why my SELECT query (Which I mentioned in previous post), was returning duplicate records. Can you please clear me one more time with an example.....Will appreciate your feedback
Let have a small demo:
create table ##Receivables
(
DOCNUMBR int identity(1,1),
CUSTNMBR int
);
insert into ##Receivables (CUSTNMBR) values (1);
insert into ##Receivables (CUSTNMBR) values (2);
insert into ##Receivables (CUSTNMBR) values (2);
insert into ##Receivables (CUSTNMBR) values (3);
insert into ##Receivables (CUSTNMBR) values (3);
select * from ##Receivables;
create table ##RM20201
(
APTODCNM int identity(1,1),
CUSTNMBR int
);
insert into ##RM20201 (CUSTNMBR) values (1);
insert into ##RM20201 (CUSTNMBR) values (2);
insert into ##RM20201 (CUSTNMBR) values (2);
insert into ##RM20201 (CUSTNMBR) values (3);
insert into ##RM20201 (CUSTNMBR) values (3);
SELECT * FROM ##Receivables As A
JOIN ##RM20201 B
ON A.CUSTNMBR= B.CUSTNMBR
WHERE A.DOCNUMBR <> B.APTODCNM
Results:
DOCNUMBR CUSTNMBR APTODCNM CUSTNMBR
3 2 2 2
2 2 3 2
5 3 4 3
4 3 5 3
Answer to 2nd question :Since you are joining CUSTNMBR which contains duplicate records results in cross join/cartesian product. This might be reason why you get duplicate records.
SELECT * FROM ##Receivables As A
LEFT OUTER JOIN ##RM20201 B
ON A.DOCNUMBR = B.APTODCNM
WHERE B.APTODCNM IS NULL
This above query returns correct results.
Answer to 1st question :It is better to join with unique column combination .
Saravanan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply