SQL help

[jino]

Hi,

I am writing a script - an applicant attends courses. The applicants may attend the sessions more than once. But I am only interested in finding out whether they have attended it at least once.

select 
cat.session1_attended_ind
from case_applicant_training cat 
where case_id=94
and applicant_id=45

script result is shown below.


session1_attended_ind
-----------------------

Y
N
Y
Y

I only want to get one row with 'Y' since this applicant has attended the session (even though more than once).

Any ideas?

Thanks

Jino

 

[SantaMufasa]

Jino,

A simple method that comes immediately to mind is:

Select 'Applicant has attended at least once.'
  from dual
 where exists
  (select 'x'
     from case_applicant_training cat 
    where case_id=94
      and applicant_id=45);

Let us know if this provides utility you seek.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[BJCooperIT]

select  'Applicant has attended at least once.'
from    dual
where   0 < (select count(*)
             from   case_applicant_training cat 
             where  case_id      = 94
             and    applicant_id = 45
             and    cat.session1_attended_ind = 'Y'
            );

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[carp]

I think the most efficient approach is a combination of Santa's and BJ's responses:

Select 'Applicant has attended at least once.'
  from dual
 where exists
  (select 'x'
     from case_applicant_training cat 
    where case_id=94
      and applicant_id=45
      and cat.session1_attended_ind = 'Y'
  );

Using the count(*) approach may require traversal of the entire table (depending on your indexes and distribution of data). Using "where exists" stops as soon as it finds a row that meets your criteria.

 

[BJCooperIT]

Good point!

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[SantaMufasa]

Duh! Sorry I missed the "...and cat.session1_attended_ind = 'Y'" part...that is vital. Good catch, both of you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.