how do I exclude from the where clause? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

how do I exclude from the where clause?

Author

Topic

kifeda
Posting Yak Master

136 Posts

Posted - 2008-10-21 : 16:00:16

I have a sql statement that works fine except for when one thing. I want to return the full record set but if I reference the column in the where statement and the field is empty the entire recordset is blank.

Here is the statement:

SELECT cl.intsignatureid, ju.strfirstname+' '+ju.strlastname as authorUpdate, cl.dtdateupdate, cl.strreason, cl.dtdatecreate as thedatecreate, c.strfirstname + ' '+c.strlastname as clientname, cl.intcaseloadid, cl.strcontactNote, c.intclientid, i.intitpid, cl.intprocedurecode, cl.bitentrytype, cl.strstatus, cl.dtdatecreate, cl.dtdateoftreatment, cl.intauthorid, a.strfirstname+' '+a.strlastname as thetherapist, cl.dtstarttime, dtendtime
FROM tbltherapistCaseLoad cl inner join tblclients c on c.intclientid = cl.intclientid left outer join tblitps i on i.intitpid = cl.intitpid inner join tbladmin a on a.intadminid = cl.intauthorid left outer join tbladmin j on j.intlocationid =c.intlocationid left outer join tbladmin ju on ju.intadminId = cl.intauthorupdate
WHERE j.strtitle='Managerial, Clinical Manager'
ORDER BY cl.intcaseloadid desc

if the title does not equal clinical manager I still want to return the rest of the information in the recordset. How do I do that?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-21 : 16:10:45

not sure if i got you...

quote:
SELECT cl.intsignatureid, ju.strfirstname+' '+ju.strlastname as authorUpdate, cl.dtdateupdate, cl.strreason, cl.dtdatecreate as thedatecreate, c.strfirstname + ' '+c.strlastname as clientname, cl.intcaseloadid, cl.strcontactNote, c.intclientid, i.intitpid, cl.intprocedurecode, cl.bitentrytype, cl.strstatus, cl.dtdatecreate, cl.dtdateoftreatment, cl.intauthorid, a.strfirstname+' '+a.strlastname as thetherapist, cl.dtstarttime, dtendtime
FROM tbltherapistCaseLoad cl inner join tblclients c on c.intclientid = cl.intclientid left outer join tblitps i on i.intitpid = cl.intitpid inner join tbladmin a on a.intadminid = cl.intauthorid left outer join tbladmin j on j.intlocationid =c.intlocationid and j.strtitle=''Managerial, Clinical Manager' left outer join tbladmin ju on ju.intadminId = cl.intauthorupdate
~~WHERE j.strtitle='Managerial, Clinical Manager'~~
ORDER BY cl.intcaseloadid desc

Webfred

Planning replaces chance by mistake

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-21 : 16:12:19

[code]SELECT cl.intsignatureid,
ju.strfirstname + ' ' + ju.strlastname as authorUpdate,
cl.dtdateupdate,
cl.strreason,
cl.dtdatecreate as thedatecreate,
c.strfirstname + ' ' + c.strlastname as clientname,
cl.intcaseloadid,
cl.strcontactNote,
c.intclientid,
i.intitpid,
cl.intprocedurecode,
cl.bitentrytype,
cl.strstatus,
cl.dtdatecreate,
cl.dtdateoftreatment,
cl.intauthorid,
a.strfirstname + ' ' + a.strlastname as thetherapist,
cl.dtstarttime,
dtendtime
FROM tbltherapistCaseLoad as cl
inner join tbladmin as a on a.intadminid = cl.intauthorid
inner join tblclients as c on c.intclientid = cl.intclientid
left join tbladmin AS j on j.intlocationid = c.intlocationid
and j.strtitle = 'Managerial, Clinical Manager'
left join tblitps as i on i.intitpid = cl.intitpid
left join tbladmin as ju on ju.intadminId = cl.intauthorupdate
ORDER BY cl.intcaseloadid desc[/code]

E 12°55'05.63"
N 56°04'39.26"

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-21 : 16:15:42

if j.strtitle column contains NULL yes, ALL BLANK
use
ISNULL(j.strtitle,'') instead

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-21 : 16:18:00

quote:
Originally posted by hanbingl

if j.strtitle column contains NULL yes, ALL BLANK
use
ISNULL(j.strtitle,'') instead

On an OUTER joined table?

E 12°55'05.63"
N 56°04'39.26"

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-21 : 16:30:47

on crack again

kifeda
Posting Yak Master

136 Posts

Posted - 2008-10-21 : 17:07:06

lol "on crack again" I guess you all are friends. I tried the statement posted by peso and that seemed to work though I'm trying to see if the one posted by hanbinl works as well

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources