I'm using a SQL Server 2000 view - and if I use a WHERE clause I get no records. If I change the filter so it goes at the join level, it works. I have heard that it's best to use joins rather than where clauses for later versions of SQL, but in both cases I'm establishing an inner join using a join clause.
Can anyone tell me why this where clause wouldn't work?
SELECT
dbo.FD__TREATMENT_STATUS.OP__DOCID AS TSDOC_ID, dbo.FD__CHILD_ABUSE.RPDate AS CPSDateRept,
dbo.FD__TREATMENT_STATUS.AdmitDate, dbo.FD__TREATMENT_STATUS.DischargeDate
FROM
dbo.FD__TREATMENT_STATUS
INNER JOIN
dbo.FD__CHILD_ABUSE ON dbo.FD__TREATMENT_STATUS.Client_ID = dbo.FD__CHILD_ABUSE.Client_ID
WHERE
dbo.FD__TREATMENT_STATUS.AdmitDate < dbo.FD__CHILD_ABUSE.RPDate AND
dbo.FD__TREATMENT_STATUS.DischargeDate > dbo.FD__CHILD_ABUSE.RPDate
I don't get an error message, but it returns no records.
Yet this one gives me the desired recordset:
SELECT
dbo.FD__TREATMENT_STATUS.OP__DOCID AS TSDOC_ID, dbo.FD__CHILD_ABUSE.RPDate AS CPSDateRept,
dbo.FD__TREATMENT_STATUS.AdmitDate, dbo.FD__TREATMENT_STATUS.DischargeDate
FROM
dbo.FD__TREATMENT_STATUS
INNER JOIN
dbo.FD__CHILD_ABUSE ON dbo.FD__TREATMENT_STATUS.Client_ID = dbo.FD__CHILD_ABUSE.Client_ID AND
dbo.FD__TREATMENT_STATUS.AdmitDate < dbo.FD__CHILD_ABUSE.RPDate AND
dbo.FD__TREATMENT_STATUS.DischargeDate > dbo.FD__CHILD_ABUSE.RPDate
Thanks in advance.
Cindy
Can anyone tell me why this where clause wouldn't work?
SELECT
dbo.FD__TREATMENT_STATUS.OP__DOCID AS TSDOC_ID, dbo.FD__CHILD_ABUSE.RPDate AS CPSDateRept,
dbo.FD__TREATMENT_STATUS.AdmitDate, dbo.FD__TREATMENT_STATUS.DischargeDate
FROM
dbo.FD__TREATMENT_STATUS
INNER JOIN
dbo.FD__CHILD_ABUSE ON dbo.FD__TREATMENT_STATUS.Client_ID = dbo.FD__CHILD_ABUSE.Client_ID
WHERE
dbo.FD__TREATMENT_STATUS.AdmitDate < dbo.FD__CHILD_ABUSE.RPDate AND
dbo.FD__TREATMENT_STATUS.DischargeDate > dbo.FD__CHILD_ABUSE.RPDate
I don't get an error message, but it returns no records.
Yet this one gives me the desired recordset:
SELECT
dbo.FD__TREATMENT_STATUS.OP__DOCID AS TSDOC_ID, dbo.FD__CHILD_ABUSE.RPDate AS CPSDateRept,
dbo.FD__TREATMENT_STATUS.AdmitDate, dbo.FD__TREATMENT_STATUS.DischargeDate
FROM
dbo.FD__TREATMENT_STATUS
INNER JOIN
dbo.FD__CHILD_ABUSE ON dbo.FD__TREATMENT_STATUS.Client_ID = dbo.FD__CHILD_ABUSE.Client_ID AND
dbo.FD__TREATMENT_STATUS.AdmitDate < dbo.FD__CHILD_ABUSE.RPDate AND
dbo.FD__TREATMENT_STATUS.DischargeDate > dbo.FD__CHILD_ABUSE.RPDate
Thanks in advance.
Cindy