Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Where clause vs. Inner Join

Status
Not open for further replies.

CindyK

Technical User
Jan 29, 2001
173
US
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
 
Beats me. I tried a similar query in DB2, splitting up the Inner Join and the Where, and the results came back just fine. That's the first time I've ever seen an Inner Join split that way. Usually you put it all in the Inner Join or all in the Where clause. Either way, it's an Inner Join to the RDBMS. I've seen outer joins split up in order to manipulate the output but not Inners.

Maybe SQL Server just doesn't like its Inner Joins split up?
 
They should return the same thing.
Have a look at the execution plan

Do you have the latest service pack?
If it's really doing this then it looks like a bug.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top