Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
Query challenge 6: composite key not in table
I avoid composite keys, but this is imported data. I have task and manhours data specified by contract (5 charcter), tasknum(int), and subtask (9 character). I have these fields in a subtask table that holds start/stop dates, authorized hours, etc. Expended hours are imported into a manhours table with these same three fields, with hours spent. I join the tables on the three similar fields to compare authorized and expended hours for each subtask.
Often manhours records come in with subtasks that aren't in our subtask table due to various errors. Boss wants a query that will catch those. It would be easy to say SELECT subtask FROM manhours WHERE subtask NOT IN (SELECT subtask FROM subtasks). But what if the subtask was entered, but in the wrong task? I want this errors query to catch that one too. All I can think of is SELECT subtask, contract+STR(tasknum)+subtask AS contasksub FROM manhours WHERE contasksub NOT IN (SELECT contract+STR(tasknum)+subtask AS dummy FROM subtasks). This query was painfully slow. Of course, I need an index on the expression contract+STR(tasknum)+subtask in order for it to be optimized. I have seen threads here that warn of the dangers of loading up with too many indexes, especially with larger tables. I can't help thinking that there must be a better way.
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement