Oracle 11 query strange INNER JOIN behavior

Greetings.
I've got a problem with this query:
SELECT * 
FROM   sincrpar_tite c 
       INNER JOIN (SELECT ptserial, 
                          ptnumpar, 
                          ptdatsca, 
                          pttipcon, 
                          ptcodcon, 
                          ptcodval, 
                          ptmodpag, 
                          pt_segno, 
                          pttotimp, 
                          ptflcrsa 
                   FROM   sincrpar_tite 
                   WHERE  e.ptroword >=- 1 
                          AND ptorigsa <> 'S') e 
         ON c.ptnumpar = e.ptnumpar 
            AND c.ptdatsca = e.ptdatsca 
            AND c.pttipcon = e.pttipcon 
            AND c.ptcodcon = e.ptcodcon 
            AND c.ptcodval = e.ptcodval 
            AND c.ptmodpag = e.ptmodpag 
WHERE  c.ptroword >=- 1 
       AND ptorigsa <> 'S' 
       AND c.ptserial = '0000014198' 

Open in new window


It returns 3 rows of which the first one is completely wrong. The wrong row is different from the others in all the JOIN conditions although it fulfills the "WHERE" filters.

I think it's a problem with INNER JOIN, can you help me?
I'm using Oracle 11.
systematicaAsked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
what is difference in bothe ???
both are same
0
 
Pratima PharandeCommented:
try this

 SELECT c.*
FROM   sincrpar_tite c
       INNER JOIN (SELECT ptserial,
                          ptnumpar,
                          ptdatsca,
                          pttipcon,
                          ptcodcon,
                          ptcodval,
                          ptmodpag,
                          pt_segno,
                          pttotimp,
                          ptflcrsa
                   FROM   sincrpar_tite x
                   WHERE  x.ptroword >=- 1
                          AND x.ptorigsa <> 'S') e
         ON c.ptnumpar = e.ptnumpar
            AND c.ptdatsca = e.ptdatsca
            AND c.pttipcon = e.pttipcon
            AND c.ptcodcon = e.ptcodcon
            AND c.ptcodval = e.ptcodval
            AND c.ptmodpag = e.ptmodpag
WHERE  c.ptroword >=- 1
       AND c.ptorigsa <> 'S'
0
 
systematicaAuthor Commented:
Same results.
As you can see from the attached image the first row is obviously wrong
result.JPG
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Pratima PharandeCommented:

Why first row is wrong ?

it fullfills requirements
c.ptroword >= -1
       AND c.ptorigsa <> 'S'
0
 
Pratima PharandeCommented:
if you change the condition
 c.ptroword > -1

then will not come that row
0
 
systematicaAuthor Commented:
Well indeed, but if I "INNER" join the tables on

 c.ptdatsca = e.ptdatsca AND
pttipcon = e.pttipcon AND
c.ptcodcon = e.ptcodcon

like I do you'll see that these 3 values are different. I didn't expect this on an INNER JOIN, I would have expected this result from a left or full join.

Am i mistaken?
0
 
Pratima PharandeCommented:
not sure but I am not getting question correctly

in inner join you have the condtions
 WHERE  x.ptroword >=- 1
                          AND x.ptorigsa <> 'S')

same are in Where cluase .....

offcourse in innerjoin you will get all values are same in both innser join
0
 
systematicaAuthor Commented:
Where conditions apply to the first table (c) and where conditions in parenthesis apply to the second select table (e).

If I don't filter for the fields in the where clause it would get me 300+ rows, the filters are needed.

Maybe I'm mistaken, but in SQL the JOIN filters are calculated before the WHERE clause, so if I have 300+ rows from a JOIN clause I will filter them with the WHERE.

Does Oracle works differently? Isn't there the same difference as in SQL between an INNER join and a FULL one?
0
 
Pratima PharandeCommented:
In inner join your 2nd table is query
which has where conditions right ?
0
 
systematicaAuthor Commented:
Let me clarify.

First table (c):
SELECT * FROM   sincrpar_tite c WHERE  c.ptroword >=- 1AND c.ptorigsa <> 'S'

Second table (e):
SELECT ptserial, ptnumpar, ptdatsca, pttipcon, ptcodcon, ptcodval, ptmodpag, pt_segno, pttotimp, ptflcrsa FROM   sincrpar_tite WHERE  e.ptroword >=- 1 AND ptorigsa <> 'S'

The INNER JOIN clause joins these two.
0
 
systematicaAuthor Commented:
Besides that (it's part of a bigger trigger) it should be the most external where to define the actual filters for the query). You gave me an idea, however. I will try to change the inner filter and run some tests.
0
 
yuchingConnect With a Mentor Commented:
Hi systematica:

1) It's seems to me both queries are from the same table which inner join does not makes any sense.
First query will produce the desired result, why you need to inner join on the same table with the same condition?
SELECT * FROM   sincrpar_tite c WHERE  c.ptroword >=- 1AND c.ptorigsa <> 'S'

2) If first table (a) and Second table(b) are different, it could be producing 3 rows as below
Table A (ptroword , ptorigsa , col3)  --> 3 rows selected
Row1 --> -1, A, C1
Row 2 --> 3, A, C2
Row 2 --> 4, A, C2

Table B(ptroword , ptorigsa , col3)  --> 2 row selected
Row 1 --> 3, B, C1
When inner join a and b with col3, this can produce 3 rows.
Row 2 --> 4, B, C2
SELECT ptserial, ptnumpar, ptdatsca, pttipcon, ptcodcon, ptcodval, ptmodpag, pt_segno, pttotimp, ptflcrsa FROM   sincrpar_tite WHERE  e.ptroword >=- 1 AND ptorigsa <> 'S'
0
 
systematicaAuthor Commented:
Thank you both, you helped me to understand the problem. There was in fact something I overlooked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.