Modify SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Modify SQL

Author

Topic

REEPER
Yak Posting Veteran

53 Posts

Posted - 2006-09-15 : 11:29:15

I have the following sql statement:

Select d.* , case when o.documentid is null then 0 else 1 end as [CheckedOutStatus]
from DMS_Detail d
left join DMS_Main o
on o.DocumentID = d.DocumentID
Order By EditDate DESC

It works great but I need to modify it so I can put the 'Where' clause on a field from DMS_DETAIL. Almost like:

Select d.* , case when o.documentid is null then 0 else 1 end as [CheckedOutStatus]
from DMS_Detail Where MyField = 'TestVal' d
left join DMS_Main o
on o.DocumentID = d.DocumentID
Order By EditDate DESC

Which of course doens't work.

MCP, MCSD

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-15 : 11:41:51

WHERE's goes before ORDER BY but after all JOINs...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-15 : 11:49:39

If you still don't get it

Select     d.*,
           case when o.documentid is null then 0 else 1 end as [CheckedOutStatus]
from       DMS_Detail d
left join  DMS_Main o on o.DocumentID = d.DocumentID
Where      d.MyField = 'TestVal'
Order By   d.EditDate DESC

Peter Larsson
Helsingborg, Sweden

REEPER
Yak Posting Veteran

53 Posts

Posted - 2006-09-15 : 12:16:11

Yup that did it - thx!

MCP, MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources