A select from join tables issue [message #10148] |
Tue, 06 January 2004 08:56 |
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 |
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 |
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 |
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.
|
|
|