sql server programming: NEED Joins or any other better option -- sql server programming

NEED Joins or any other better option

xgopi
11/7/2006 7:44:01 PM

sql server programming:

HI,

The existing query which is biult is an asp app is as follows

select ID,Name from [view_ReportTracK] with (NOLOCK) where
(

(id in (select id from view_orderchanges with (NOLOCK) where
ODate >= convert(datetime, '1/1/2005') and
ODate < (convert(datetime, '2/1/2005') + 1)))

or

(id in (select id from view_orderOriginal with (NOLOCK) where
ODate >= convert(datetime, '1/1/2005') and
ODate < (convert(datetime, '2/1/2005') + 1)))

or

(id in (select id from view_orderDiscount with (NOLOCK) where
ODate >= convert(datetime, '1/1/2005') and
ODate < (convert(datetime, '2/1/2005') + 1)))

What Iam trying to achive is create a view V1
and the usage will be as follows

select id, name from V1
where
ODate >= convert(datetime, '1/1/2005') and
ODate < (convert(datetime, '2/1/2005') + 1)))

The issue iam facing is
since the Odate value is not know during join , there is a mismatch in the
records.

Re: NEED Joins or any other better option

vt
11/8/2006 3:45:55 PM

try this

select ID,Name from [view_ReportTracK] vr
left join(select id,ODate from view_orderchanges with (NOLOCK) rs1 on
rs.id=vr.rd and rs1.ODate>convert(datetime, @dATE1) and ODate <
(convert(datetime, @DATE2) + 1))
left join(select id,ODate from view_orderOriginal with (NOLOCK) rs2 on
rs.id=vr.rd and rs2.ODate>convert(datetime, @dATE1) and ODate <
(convert(datetime, @DATE2) + 1))
left join(select id,ODate from view_orderDiscount with (NOLOCK) rs3 on
rs.id=vr.rd and rs3.ODate>convert(datetime, @dATE1) and ODate <
(convert(datetime, @DATE2) + 1))
WHERE (RS1.ID IS NOT NULL AND RS1.ODate IS NOT NULL)
OR
(RS2.ID IS NOT NULL AND RS2.ODate IS NOT NULL)
OR
(RS2.ID IS NOT NULL AND RS3.ODate IS NOT NULL)

vinu

[quoted text, click to view]