I have this query here...
SELECT DISTINCT Coalesce(NULLIF(FullDataTable.workgrp,''),'UNKNOWN') as value, Coalesce(NULLIF(AvaDepartmentLookup.departmenttitle,''),'UNKNOWN') as title
FROM FullDataTable LEFT OUTER JOIN AvaDepartmentLookup ON FullDataTable.workgrp = AvaDepartmentLookup.departmentvalue
Where (FullDataTable.workgrp not in (select AvaDepartment_Exclusion.departmentvalue from AvaDepartment_Exclusion))
order by title
If I take the where clause out, the query runs instantly. The moment I place the where clause back in, it runs forever???
The AVADepartment_Exclusion.department value table is just a simple lookup table with and 'id' column that auto populated and a 'departmentvalue' column which holds simple string values that would match workgrp?
Am I'm overlooking something.
Also, workgrp is indexed.
Thanks for any insite
Try creating an index on AvaDepartment_Exclusion.departmentvalue and change where statement to:
Where not exists (select 1 from AvaDepartment_Exclusion where AvaDepartment_Exclusion.departmentvalue=FullDataTable.workgrp)
1 Like
AWESOME!!!
That seemed to have fixed the issue.
Can you explain a little how your where clause differed from mine (yours worked. LOL). I'm still learning alot with sql in itself.
Thanks for everything thus far though!!!
Another option would be:
SELECT DISTINCT
Coalesce(NullIf(FullDataTable.workgrp, ''), 'UNKNOWN') value
, Coalesce(NullIf(AvaDepartmentLookup.departmenttitle, ''), 'UNKNOWN') title
FROM
FullDataTable
LEFT OUTER JOIN
AvaDepartmentLookup ON FullDataTable.workgrp = AvaDepartmentLookup.departmentvalue
LEFT OUTER JOIN
AvaDepartment_Exclusion ON FullDataTable.workgrp = AvaDepartment_Exclusion.departmentvalue
WHERE
AvaDepartment_Exclusion.departmentvalue IS NULL
ORDER BY
Coalesce(NullIf(AvaDepartmentLookup.departmenttitle, ''), 'UNKNOWN');
I'll take a look at this solution also. Learning a lot from these forums.
Thx!