Home » SQL & PL/SQL » SQL & PL/SQL » A select from join tables issue
A select from join tables issue [message #10148] Tue, 06 January 2004 08:56 Go to next message
Kanac
Messages: 3
Registered: January 2004
Junior Member
The following query :
select distinct s.SERVKY
from SERVICES s ,TTBL t ,OTBL O where
s.SERVKY = t.SERVKY and o.OKEY = t.OKEY AND s.DEPKY in (49,50,53)
and o.KEY = 253733
order by s.SERVKY

This returns me the followings Rows :
81,84,96

The following :

select distinct s.SERVKY
from SERVICES s ,TTBL t ,OTBL O where
s.SERVKY = t.SERVKY and o.OKEY = t.OKEY AND
s.DEPKY in (49,50,53) and s.SERVKY > 0
and o.KEY = 253733
order by s.SERVKY

This returns me only the Rows :
81,84

Because the SERVICE 96 does not exists in the Table SERVICES but it exists in TTBL table

What i don't understand is :
The first select is not a right outer join then why it returns me the services that exists in TTBL table but not in SERVICES table
Re: A select from join tables issue [message #10149 is a reply to message #10148] Tue, 06 January 2004 09:56 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Your results make no sense to me.

The only difference between the two queries you've posted is the predicate AND s.servky > 0, which s.servky = 96 satisfies.

Can you copy and paste directly from SQL*Plus the actual queries and their results? And a representative sample of the data from services, ttbl and otbl please?

Thanks, Kanac.

Art.
Re: A select from join tables issue [message #10151 is a reply to message #10149] Tue, 06 January 2004 11:44 Go to previous messageGo to next message
Kanac
Messages: 3
Registered: January 2004
Junior Member
The following Queries returns nothing :

select distinct s.SERVKY
from SERVICES s ,TTBL t ,OTBL O where
s.SERVKY = t.SERVKY and o.OKEY = t.OKEY AND
s.DEPKY in (49,50,53) and s.SERVKY = 96
and o.KEY = 253733
order by s.SERVKY

and This
select distinct t.SERVKY
from SERVICES s ,TTBL t ,OTBL O where
s.SERVKY = t.SERVKY and o.OKEY = t.OKEY AND
s.DEPKY in (49,50,53) and t.SERVKY = 96
and o.KEY = 253733
order by s.SERVKY

Thinks
Re: A select from join tables issue [message #10152 is a reply to message #10151] Tue, 06 January 2004 11:51 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
OK, you had a typo in your original posting.

What does this return?
SELECT   s.servky   s_servky
,        t.servky   t_servky
,        COUNT(*)
FROM     services   s
,        ttbl       t 
,        otbl       o
WHERE    s.servky = t.servky
AND      o.okey   = t.okey
AND      s.depky  IN (49,50,53)
AND      o.key    = 253733
GROUP BY s.servky
,        t.servky
ORDER BY s.servky
,        t.servky
/
A.
Previous Topic: Find Values in specific columns in all tables in database-Urgent
Next Topic: Can I dynamically choose a column for a cursor in PL\SQL?
Goto Forum:
  


Current Time: Thu Mar 28 20:38:39 CDT 2024