Блог

У меня есть четыре таблицы значений: инструкторы, сертификаты, block_subjects и блоки и две таблицы отношений: instructor_certifications и subject_certification. Вот так:

 block -- block_subject
          |
          |
 subject_certification
          |
          |
     certification 
          |
          |
instructor_certification
          |
          |
      instructor
  

Мне нужен запрос, который сообщит мне, для каждого блока, какие инструкторы имеют квалификацию для обучения блоку. В частности, я не хочу указывать идентификатор блока как часть запроса; Я хочу выбрать несколько блоков на основе разных критериев.

Вот (нерабочий) запрос, который у меня есть в данный момент:

 select inst.name, inst.id
from instructor as inst
join instructor_certification as ic on inst.id = ic.instructor_fid and
ic.certification_fid = all (
   select cert.id
   from block_subject as bs 
   join subject_certification as bsc on bsc.block_fid = bs.id
   join certification as cert on bsc.certification_fid = cert.id
   where bs.id = any (
       select bs.id
   from block as b
       join block_subject as bs on b.subject_fid = bs.id
       where (b.start_date, b.end_date) overlaps (?, ?)
   )
)
  

Очевидно, это не работает, потому что ‘all’ собирает все сертификаты, необходимые для каждого block_subject в диапазоне дат.

Редактировать: Также я должен был уточнить, что на самом деле требуется несколько сертификатов для каждого block_subject.

После редактирования:

Требовать несколько сертификатов на блок для каждого преподавателя:

  select b.id as blockid, bs.subject_id as subject_id, i.id as inscructorid, count(ic.certification_id) as numCerts
 from block b
 join block_subject bs on b.id = bs.block_id
 join subject_certification sc on bs.subject_id = sc.subject_id
 join instructor_certification ic on sc.certification_id = ic.certification_id
 join instructor i on ic.instructor_id = i.id
 group by b.id, bs.subject_id, i.id
 having count(ic.certification_id) > 1
  

В вашей проблеме указано «Для каждого блока». Итак, начните с блока, а затем выполните объединения. Вот так:

  select *
 from block b
 join block_subject bs on b.id = bs.block_id
 join subject_certification sc on bs.subject_id = sc.subject_id
 join instructor_certification ic on sc.certification_id = ic.certification_id
 join instructor i on ic.instructor_id = i.id
  

Теперь вы можете добавить любые критерии, которые вы хотите.

Конкретный блок?

  where b.id = @inid
  

Диапазон дат?

  where @Date between b.start_date and b.end_date
  

Инструктор?

  where i.id = @inid
  

Сертификация?

  where c.id = @inid
  

или комбинация.

Попробуйте это.

 declare @Date datetime

set @Date = '01/01/2011'

select inst.name, inst.id
from instructor as inst
join instructor_certification as ic on inst.id = ic.instructor_fid 
and ic.certification_fid in (
   select cert.id
   from block_subject as bs 
   join subject_certification as bsc on bsc.block_fid = bs.id
   join certification as cert on bsc.certification_fid = cert.id
   where bs.id in (select bs.id 
       from block as b
       join block_subject as bs on b.subject_fid = bs.id
       where @Date between b.start_date and b.end_date ) )
   ) x
  

просто инструкторы…

  select
    i.*
 from
    instructor i on 
 where
    exists (
     select *
     from
        block b
        join block_subject bs on b.id = bs.block_id
        join subject_certification sc on bs.subject_id = sc.subject_id
        join instructor_certification ic on sc.certification_id = ic.certification_id
      WHERE 
        ic.instructor_id = i.id
        AND
        ..other filters here
        )