General discussion


SQL question

By TimMitchell ·
I am in need of some SQL expertise. I'm writing a script to access a Helpdesk database, and the results I need are in multiple tables. The main table, called CallLog, is the primary table from which I pull the data. I also need data from the Asgnmnt table, which stores the assignments for all work orders. There can be multiple entries in the Asgnmnt table for each unique work order in the CallLog table. I also pull information from the Subset table, which stores details of all the users we support.

I've been doing a query like:
SELECT CallLog.CallID, Subset.Name, Subset.Room, Asgnmnt.Assignee
FROM CallLog, Subset, Asgnmnt
WHERE CallLog.CallStatus <> 'Closed' ;

Now I'm sure you SQL experts will immediately recognize that this query pulls more than what I want, to the tune of 100,000+ results. What I want is to retrieve one result for each entry in the CallLog table, and have each CallLog result reference one single Subset record (the end user's information) and one single Asgnmnt record (the most recent assignment).

Any suggestions? Your help would be greatly appreciated.


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by jeng In reply to SQL question

In your question, I notice you're doing an INNER JOIN on three tables (CallLog, Subset, Asgnmnt) without any join conditions.

Complete the join by joining the tables on a (preferably indexed or primary key) field.

For example,
FROM CallLog,Subset,Asgnmnt
CallLog.CallStatus<>'Closed' AND CallLog.field1=Subset.field1 AND Subset.field2=Asgnmnt.field2;

Related Discussions

Related Forums