Stacy,
Modified to allow null response_no :
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
)
Hope this helps,
Mark
----- Original Message -----
From: "Stacy Mader" <[EMAIL PROTECTED]>
To: "Mitchell, Louise M" <[EMAIL PROTECTED]>
Cc: "dbi-users" <[EMAIL PROTECTED]>
Sent: Tuesday, February 13, 2001 5:34 PM
Subject: Re: Multiple table queries
> 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.
>
>
