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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Pratima PharandeCommented:

Why first row is wrong ?

it fullfills requirements
c.ptroword >= -1
       AND c.ptorigsa <> 'S'
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
Pratima PharandeCommented:
what is difference in bothe ???
both are same
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
yuchingCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.