SQL Query

[tful282]

I am reporting from an oracle 8.1.7 database.
I have 3 tables- steps, points and names.
STEPS:
task# step# point# reading
123 1 10 0.5
123 2 20 0.2
123 3

POINTS:
point# pointname
10 x
20 y

NAMES:
pointname
x
y
z

I want a sql query that for a given task# will return a pointname and a reading for each name listed in the NAMES table. If there is no corresponding pointname & number then I want to return the pointname, and null value for the reading.
Thanks in advance

 

[SantaMufasa]

TFul,

Here is my code, using your values, and the results:

SQL> select task#, n.pointname, s.reading
  2  from steps s, points p, names n
  3  where n.pointname = p.pointname(+)
  4  and p.point# = s.point# (+);

     TASK# POINT    READING
---------- ----- ----------
       123 x             .5
       123 y             .2
           z

SQL>

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:39 (15Jan04) GMT, 12:39 (15Jan04) Mountain Time)

 

[tful282]

Thanks Mufasa, but I need to select based on task#-
suppose I have some more rows in the STEPS table:

task# step# point# reading
...
345 1 10 0.9
345 2

I want to be able to say

select n.pointname, s.reading
from steps s, points p, names n
where task#=123 and......

to bring back
x 0.5
y 0.2
z

OR
select n.pointname, s.reading
from steps s, points p, names n
where task#=345 and ......

to bring back
x 0.9
y
z

I hope i'm being clear.

 

[tful282]

I found a way to do it.

select task#, pointname, max(reading)
from
(select task#, n.pointname, s.reading
from steps s, points p, names n
where n.pointname = p.pointname
and p.point# = s.point#
union
select task#, n.pointname, null as reading
from steps s, names n)
group by task#, pointname