I want to get the common records of 2 tables. However, it turns out the
count of the common records of 2 tables is even larger than the count of
table TB1. It is so weird and I have no idea why. Here's the inner join
statement I use:

SELECT TB1.*
FROM TB1 INNER JOIN
TB2 ON TB1.RMA = TB2.RMA
AND TB1.Parts = TB2.Parts
AND TB1.PPID = TB2.PPID;

and this SQL statement should get the count of the common records of 2
tables:

SELECT COUNT(*)
FROM TB1 INNER JOIN
TB2 ON TB1.RMA = TB2.RMA
AND TB1.Parts = TB2.Parts
AND TB1.PPID = TB2.PPID;

I really have no idea what's going on, since I think this SQL statement is
correct. Please advice.

Thanks!!!
John

select count(*)
from (
SELECT TB1.*
FROM TB1 INNER JOIN
TB2 ON TB1.RMA = TB2.RMA
AND TB1.Parts = TB2.Parts
AND TB1.PPID = TB2.PPID
)derived

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net



[quoted text, click to view]

The values in the table tb1/tb2 or both of them might be repeating for the
columns rma,parts,ppid. see following example.
The row with the values 1,'a',2 is repeated twice in tb1 while is repeated 3
times in tb2 Therefore if you join them it will give you 6 rows.

create table tb1( rma int, parts varchar(5), ppid int)
create table tb2( rma int, parts varchar(5), ppid int)

insert into tb1 values (1,'a',2)
insert into tb1 values (1,'a',2)
insert into tb2 values(1,'a',2)
insert into tb2 values(1,'a',2)
insert into tb2 values(1,'a',2)

SELECT TB1.*
FROM TB1 INNER JOIN
TB2 ON TB1.RMA = TB2.RMA
AND TB1.Parts = TB2.Parts
AND TB1.PPID = TB2.PPID;

make sure you have right joins ie you are not missing any additional column
in the join, if it is correct then there is ambiguity in your table, and you
will have to correct it.OR you may have to use distinct clause to eliminate
repeating rows.

SELECT distinct TB1.*
FROM TB1 INNER JOIN
TB2 ON TB1.RMA = TB2.RMA
AND TB1.Parts = TB2.Parts
AND TB1.PPID = TB2.PPID;

--
-Vishal
[quoted text, click to view]