query with joins

Can someone please re-write this query for me using better
joins? Thank you very much.
select distinct(c.research), t.FAX, t.NAME, CAST
(exp_AUDITDATE AS varchar(11))
AS AUDITDATE from teachers t (nolock), cases c (nolock),
RecordLog l (nolock)
where l.postedDate > 120703 and
l.postedDate < GETDATE() and l.seqno = c.sequence_no and
l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
Ida"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx
.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida
Here it is. It has distinct in it so it's probably broken, but without DDL
and a description of what you want, it's impossible to tell how broken.
Also it converts a date to a string, which it probably shouldn't do.
select distinct
c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
from
cases c (nolock)
join teachers t (nolock)
on c.research = t.resNo
join RecordLog l (nolock)
on l.seqno = c.sequence_no
and l.PostedDate = c.PostedDate
where l.postedDate > 120703
and l.postedDate < GETDATE()
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
David|||Here's a start:
select distinct c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
from teachers t (nolock)
join cases c (nolock) ON c.research = t.resNo.
join RecordLog l (nolock) ON l.seqno = c.sequence_no
where l.postedDate > '20031207'
and l.postedDate < GETDATE()
and l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx
.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida|||Thank you so much
Ida

>--Original Message--
>Here's a start:
>select distinct c.research,
> t.FAX,
> t.NAME,
> CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
>from teachers t (nolock)
>join cases c (nolock) ON c.research = t.resNo.
>join RecordLog l (nolock) ON l.seqno = c.sequence_no
>where l.postedDate > '20031207'
> and l.postedDate < GETDATE()
> and l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
> or exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE'
>
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message
> news:2971601c465e6$7c924260$a601280a@.phx
.gbl...
better[vbcol=seagreen]
(nolock),[vbcol=seagreen]
and[vbcol=seagreen]
>
>.
>|||Thank you.

>--Original Message--
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message
> news:2971601c465e6$7c924260$a601280a@.phx
.gbl...
better[vbcol=seagreen]
(nolock),[vbcol=seagreen]
and[vbcol=seagreen]
>Here it is. It has distinct in it so it's probably
broken, but without DDL
>and a description of what you want, it's impossible to
tell how broken.
>Also it converts a date to a string, which it probably
shouldn't do.
>select distinct
>c.research,
>t.FAX,
>t.NAME,
>CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
>from
>cases c (nolock)
>join teachers t (nolock)
> on c.research = t.resNo
>join RecordLog l (nolock)
> on l.seqno = c.sequence_no
> and l.PostedDate = c.PostedDate
>where l.postedDate > 120703
>and l.postedDate < GETDATE()
>and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
>AND DELIVERY <> 'INACTIVE'
>David
>
>.
>