A while back I asked about querying two tables linked by a common field
(see
http://www.xray.mpe.mpg.de/mailing-lists/dbi/2001-02/msg00252.html).
Instead of typing alot of code, I have simply joined the two tables
together
like this:
create view report_response as
(select a.fault_no,
issue,
date_occurred,
one_line_summary,
allocated_to,
fix_status,
response_no,
response_short,
reported_by,
contact_person,
response
from report a, response b
where a.fault_no = b.fault_no(+));
Now, if I issue the following SELECT command:
SELECT fault_no ,one_line_summary, response_no FROM stacy WHERE fault_no
= 1378
I get what I'm asking for, but the problem is that there can be multiple
response_no's
for each fault_no. For example, from the above SQL:
FAULT_NO,ONE_LINE_SUMMARY,RESPONSE_NO
'1378','UPS crash --> ME / Anemometer crash','816'
'1378','UPS crash --> ME / Anemometer crash','813'
'1378','UPS crash --> ME / Anemometer crash','814'
In my previous post, the problem of duplication with two tables
was solved by the following:
Select a.fault_no,
b.response_no,
b.category
From report a,
response b
Where a.fault_no = b.fault_no(+)
ANd ( b.response_no = ( select max(response_no)
from response
where a.fault_no = b.fault_no )
or b.response_no is null
)
Now with the joined view, this obviously won't work, but I'm
clueless as to how I can modify the above to work with
a joined view. Any ideas?
Regards,
Stacy.
