Filter keeping null values

[Crimpage]

Hi everyone,

 

Hopefully this is simple enough. I am doing a query from an IT Helpdesk database. The database keeps the request and the notifications (emails) in serperate tables linked by a notification_request table. A quick rundown on linking fields

 

 

Request table:

workorderid

 

Notification_workorder table:

workorderid

notificationid

 

notification table:

notificationid

 

So there can be multiple notifications to each request/workorder and from multiple senders. What I want to do, it filterout all notifications from senderid 1, and if by doing that, instead of not showing the request all together, to show the request, but with null notificationdate and senderid values.

 

How it looks at the moment:

 

Requestid  createdtime (all other workorder fields) notificationid  senderid  notificationdate

100              01/05/2008 07:00:00                                50                1          01/05/2008 07:00:00

100              01/05/2008 07:00:00                                51                3          01/05/2008 07:00:00

101              01/05/2008 08:00:00                                52                1          01/05/2008 01:00:00

 

In this case, when I filter the senderid, it will still show the result line for senderid 3 from request 100, but if the only sender was 1 like request 101, it wont show that request at all. What I would like it to show is:

 

Requestid  createdtime (all other workorder fields) notificationid  senderid  notificationdate

101              01/05/2008 08:00:00                                NULL          NULL          NULL

 

So I can still see the request, but easily see, that no one has responded to it yet.

 

Here is the query I use, as you can see, the last line filters out the senderid of 1, and subsequently the requests that only have 1 as a sender.

 

SELECT wo.workorderid, wo.createdtime, wo.title, nw.notificationid, note.senderid
FROM servicedesk.WorkOrder_Threaded wot
LEFT JOIN servicedesk.WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID
LEFT JOIN servicedesk.WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN servicedesk.SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN servicedesk.AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN servicedesk.notify_workorder nw on nw.workorderid = wo.workorderid
LEFT JOIN servicedesk.notification note on note.notificationid = nw.notificationid
WHERE wo.createdtime > (UNIX_TIMESTAMP('2008-04-01 00:00:00') * 1000)
AND wo.createdtime < (UNIX_TIMESTAMP('2008-04-01 23:59:59') * 1000)
AND wo.CREATEDTIME != 0
AND wo.CREATEDTIME IS NOT NULL
AND wot.THD_WOID=wot.WORKORDERID
AND note.senderid <> 1

 

Cheers,

Dave

[fenway]

A few things:

 

1) you likely don't need left joins except for the tables with senderid and notificationdate.

2) it would be preferable to store createdtime as a proper datetime field or timestamp, not integer.

3) your date restriction for created time means you don't need != 0 or IS NOT NULL.

4) if you simply want to null out everything for senderid = 1, that's different than excluding them altogether.

[Crimpage]

Hi Fenway,

 

Thanks for your response. I am connecting to an applications database, it is not one that I have created, so I don't have a choice with the formats they have chosen to store their dates in.

 

I don't just want to NULL the values out, as SENDERID 1 could have sent 4 notifications for the 1 workorder and nothing else, so I don't want to see the workorder show up as 4 result lines all with null values.

 

If senderid 1 was the only person to send a notification of any sort, I would like to see the workorder show once with null values in the senderid and notificaitontime fields.

 

I haven't given all of the fields I will be using in the SELECT statement. The other LEFT JOINS join the users tables and workorderstates tables.

 

Any help would be appreciated.

 

Cheers,

Dave.

[fenway]

Why not simply check the value of senderid using an IF() ,and null them out yourself?