Hi,
I am only a very occasional mySQL user and I need some help with a join.
Our internal helpdesk is done in mySQL and PHP. I have been asked to dump
some of the contents of the database into a file for loading into a data
warehouse.
The following query is getting the data from the workorders (helpdesk call)
table and getting some normalised data from other lookup tables. However
the ClosedBy field is sometimes NULL (if the call has not been closed).
I want to get everything from this table, even if there is no corresponding
record in the personnel table. At the moment if workorders.closedby is NULL
then i do not get the record from the workorders table.
my query is:
select workorders.jcn AS "WO Number",
workorders.seq AS "WO Seq",
workorders.contact AS Contact,
workorders.summary AS Summary,
class.name AS Class,
severities.name AS Severity,
DATE_FORMAT(workorders.createdon, '%Y-%m-%d') AS WOCreatedOn,
CONCAT(personnel.lastname, ", ", personnel.firstname) as ClosedBy,
workorders.closedon AS WOClosedOn,
workorders.totalhours AS TotalHours,
accounts.name AS Customer,
products.name AS CallCatagory,
statuses.name AS Status
FROM workorders, class, severities, personnel, accounts, products, statuses
WHERE class.id = workorders.clid
and severities.id = workorders.severity
and (workorders.closedby = personnel.id OR workorders.closedby IS NULL)
and accounts.id = workorders.account
and products.id = workorders.product
and statuses.id = workorders.status
ORDER BY workorders.jcn;
Can anyone help please?
TIA,
Ciaran.
Ciarán O'Neill
IT Department,
United Drug Plc,
Magna Business Park,
Citywest Road,
Dublin 24.
* +353 1 463 2535
Fax +353 1 463 2397
* www.united-drug.com
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**********************************************************************
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
