A difficult query using 2 tables, help!!

[prunera]

Hi Team!

Anybody could help me with this hell query?

I got 2 tables:

Table: STUDENTS with only 1 field: [name]
id1: jose
id2: william
id3: john
id4: sandra

Table: COURSES with 2 fields: [name], [course]
id1: jose, saxo
id2: william, cook
id3: john, saxo
id4: sandra, garden

I wanna make something "as easy" like a query that displays ALL the students, and, on the right side of each student, a 1 if that student is a student of the saxo course or 0 if don't.

I'm sure that it is easy! but I'm not success with that.

Thanks in anticipation for your help !

David Prunera,
BARCELONA (F.C. BARCELONA ohe!! ohe!! oheee!!)

[fgguzman]

Assuming that a student may have 0 or 1 courses associated I suggest the following ( in transac SQL language)

SELECT A.name, CASE WHEN ISNULL(B.course,'') =  'saxo' THEN 1 ELSE 0 END as InSaxoCourse
FROM STUDENTS A LEFT JOIN COURSES B ON (A.name = B.name)

If a student can have 0,1 or more courses then I would do this

SELECT A.name, CASE WHEN ISNULL(B.course,'') =  'saxo' THEN 1 ELSE 0 END as InSaxoCourse
FROM STUDENTS A LEFT JOIN COURSES B ON (A.name = B.name AND B.course = 'saxo')

The left join makes sure all records from table A appear in the result set at least once. When a record on table A does not have a corresponding record on table B, then all columns from table B in the result set are NULL.

Hope this helps!

[DB_err]

I would try this

select s.name, 
case when when c.course = 'saxo' then 1 else 0 end as enrollment
from students s left outer join courses c on s.name = c.name

[prunera]

It works!
Thank u very much