Louise,
Thanks, that works great. One thing though: what about when response_no
= null? These don't show!!!
Regards,
Stacy.
"Mitchell, Louise M" wrote:
> Stacy,
>
> I'm assuming you want the max response_no per fault_no
>
> I think the following should do it..
>
> 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 )
>
> This is Oracle-ish sql... hope this helps..
>
> L
>
> -----Original Message-----
> From: Stacy Mader [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, February 13, 2001 4:28 PM
> To: dbi-users
> Subject: Multiple table queries
>
> Hi all,
>
> I am trying to perform a query from two tables linked by the field
> fault_no. My SQL is:
>
> SELECT report.fault_no,
> response.response_no,
> response.category
> FROM report,
> response
> WHERE report.fault_no = response.fault_no(+)
>
> Now the problem with this statement is that some of the results are
> repeated. For example:
>
> '1189','460','Other'
> '1189','457','Telescope control system'
> '1189','653','Drive system - az'
> '1190','451','Telescope control system'
> '1190','465','Telescope control system'
>
> The reason for this is that although fault_no is distinct, a fault_no
> can have more than one
> response_no!
>
> So my question is: for fault_no's with multiple response_no's, can
> I modify the SQL to just
> return results with the latest response_no only? For example, from the
> above query, I'd
> like to have returned:
>
> '1189','653','Drive system - az'
> '1190','465','Telescope control system'
>
> Thanks in advance,
>
> Regards,
> Stacy Mader.
