Alternative SQL Query

[TastyWheat]

I have a query worked out with the exact result I'm looking for, but it's kind of slow. Can someone find another way to write up this query?

SELECT EID, ShortName, ClassID
FROM (
[COLOR="SeaGreen"]     SELECT Employee.EID, Class.CID, Class.ShortName
     FROM Employee, Class[/COLOR]
) AS Temp1
LEFT JOIN [Class Attendance]
ON (Temp1.CID=[Class Attendance].ClassID) AND
   (Temp1.EID=[Class Attendance].EmployeeID);

The inner (green) query pairs every employee with every class they could possibly take (cross product). Then that query is joined with the attendance table to show who has taken which class. If a class hasn't been taken the ClassID will be null like so:

EID         ClassID ShortName
=========================
111-11-1111 1       ISM
111-11-1111 2       ABC
111-11-1111         BGP
222-22-2222         ISM
222-22-2222 2       ABC
222-22-2222 3       BGP

So is there an easier way to get this result?

 

[KeithG]

So you are taking the Cartesian product and Left Join the Attandance table? The only other way I can think of is a Union query. One SQL to show each class each Employee has taken and one SQL to show each class that each employee hasn't taken.

 

[TastyWheat]

Thanks for the suggestion, but I don't know if it's possible that way. Getting a list of classes taken is easy, but getting the classes not taken is harder. The only way I know how is to use an EXISTS or IN clause, but that requires me to use outer query data in the inner query. Crosstab queries won't accept anything like that so my options are limited.

 

[The_Doc_Man]

Hmmm... you must not have a lot of employees or a lot of classes, since this otherwise is a VERY BIG query. Which is why you have such a slow query, perhaps. But that inner SELECT is what eats your lunch.

Assuming your attendance record is essentially a junction table of the class and employee tables with one extra field, what you want to do is promote the inner select.

Perhaps the Cartesian join of the employees table as the main query with an outer join with the attendance table? I think that your slowness is due to the fact that the inner query needs to be re-evaluated for every case of the outer query. If you change it so that the cartesian join is the outer loop and the join to the junction table is subordinate, that might help. Even if you have to make the cartesian join its own query and then write the outer join as a second-layer query, treating the first query as the master record doesn't cause the cartesian join to be reevaluated so often.

 

[TastyWheat]

I vaguely mentioned this earlier, but this query is going to be converted into a crosstab query that looks like this:

EID          ISM  ABC  BGP
==========================
111-11-1111  1    1    0
222-22-2222  0    1    1

The difference between the running time of the select and crosstab query is very big, but I assumed I couldn't improve the crosstab query at all. Whatever I do I cannot accept null values in the final crosstab query.

Your suggestion makes sense but I don't see how I can move around the cartesian join. I've tried several methods and nothing else gets me the result I need in any amount of time.