Select Statement Problem

[farid25]

I have 3 tables

t_studends
---------
student_id student_name
---------- ------------

1 Student1
2 Student2
3 Student3


t_course
---------
course_id course_name
-------- -----------
1 word
2 excel
3 outlook
4 powerpoint

t_couse_student
---------------
id student_id course_id
-- ---------- ---------
1 1 1
2 1 2 *************
3 2 1

when i search for students whom didn't took excel for example, the query returns all 3 students, i want it

return only student 2,3 because student 1 took that course.

can u help me plz...

[Banfa]

Post the query you are/where using

[farid25]

query am using
-----------------
select *
from t_studends,t_course,t_couse_student
where t_studends.student_id = t_course.student_id AND t_couse_student .course_id =t_course.course_id AND t_course.course_id <> 2

[lipsa]

helo farid,i m a beginner wid 3 months work exp :) ,but trying to help u,hope it will be useful.i m writing 2 queries.i am assuming data frm ur table

assumption-ts.student_name(PK)-tcs.student_name(FK)
tc.course_id(PK)-tcs.course_id(FK)
Query-1
select distinct ts.student_name
from t_students ts,t_course tc,t_course_student tcs
where ts.student_id=tcs.student_id and tc.course_id=tcs.course_id
and tcs.course_id not in(select course_id from t_course where course_name='excel')

Output-student1,student2
Explanation-student1 's name comes as it takes 'word' course also besides 'excel'.student2 takes 'word'.and student3 wont b there as u hv nt mentioned its corresponding course in t_course_student.

Query-2
Note-this query return name of the students who have not taken 'excel'(with other course like student1) at all

select ts.student_name
from t_students ts,t_course tc,t_course_student tcs
where ts.student_id=tcs.student_id and tc.course_id=tcs.course_id
and ts.student_id not in (select student_id from t_course_student where course_id =(select course_id from t_course where course_name='excel'))

Output-student2
Regards,
Lipsa(plz excuse me and provide right solution if i m wrong :) )

[Banfa]

Thanks lipsa, I'm not a great SQL expert and I had got as far as working out that negative requirements where not easy. From your solution I see that this was probably a justified conclusion.

[lipsa]

sorry Banfa,i dont get u clearly.
but if the solution serves the purpose..umm...its ok..:-))

[farid25]

lipsa, thanks very very much
it works now , thanks again for your help

[lipsa]

My pleasure Farid :)
Regards,
Lipsa