SQL Help

From: <cmwatson_at_cal.ameren.com>
Date: Wed, 11 Jul 2007 12:32:01 -0700
Message-ID: <1184182321.999728.9600@57g2000hsv.googlegroups.com>

I've tried SQL to come up with the result below but am having no luck. Please, can someone help me!!!!!:) Believe me, any and all help will be greatly appreciated.

What I need is the task_ids that have actv_code.short_name like '%FIN' where actv_code.code = 'Lead Craft' and no actv_code.code that equals Outage Code

task table
task_id task_code

  1         W123456
  2         07146566
  3         07146567
  4         06230001
  5         06123321
  6         06496334
  7         W642121
  8         05462111

actv_code table
task_id  code                 short_name
   1       Outage Code         R16
   4       Outage Code         R15
   6       Outage Code         R16
   1       Lead Craft             ZFM
   5       Lead Craft             EFIN
   6       Lead Craft             MFIN
   7       Lead Craft             IFIN
   8       Outage Code         R16
   8       Lead Craft             MFIN

Result Set

task_id task_code

   5        06123321
   7        W642121

This is the SQL I'm using. It doesn't return any rows. I think I've tried this a dozen different ways but nothing returns the result set that I need.

select t.task_id, t.task_code,
(select short_name from taskactv, actvcode
  where taskactv.task_id = t.task_id

      and actvcode.actv_code_id = taskactv.actv_code_id
      and taskactv.actv_code_type_id = (select actv_code_type_id
                      from actvtype
                      where actv_code_type = 'Outage Code')) as

outage,

(select short_name from taskactv, actvcode

   where taskactv.task_id = t.task_id

      and actvcode.actv_code_id = taskactv.actv_code_id
      and taskactv.actv_code_type_id = (select actv_code_type_id
                      from actvtype
                      where actv_code_type = 'Lead Craft')) as

LeadCraft
from task t
where t.task_id in
(

    ( select taskactv.task_id "id" from taskactv

        where
             taskactv.actv_code_id in (select actv_code_id
                      from actvcode
                      where short_name like '%FIN'
                      and actv_code_type_id = (select
actv_code_type_id
                                  from actvtype
                                  where actv_code_type = 'Lead
Craft'))
      and
            taskactv.actv_code_id not in (select actv_code_id
                          from actvcode
                          where  short_name is null and
                              actv_code_type_id = (select
actv_code_type_id
                                        from actvtype
                                        where actv_code_type = 'Outage

Code'))

      group by task_id having count(task_id) >= 2 ) ) Received on Wed Jul 11 2007 - 14:32:01 CDT