0

I have this sql query, which I have to provide ID to work (it`s 100 in this example)

SELECT * FROM students_subjects WHERE student_id 
NOT IN 
(SELECT student_id FROM students_info WHERE class_id=(SELECT class_id FROM groups WHERE group_id=(SELECT group_id FROM teacher_groups_subjects WHERE t_g_s_id=**100**)))

I want to select all rows from table teacher_groups_subjects and run the query above for each ID (t_g_s_id)

Anyone?

user1799573
  • 35
  • 1
  • 5

1 Answers1

0

Possibly like this:-

SELECT * 
FROM students_subjects a 
LEFT OUTER JOIN students_info b ON a.student_id = b.student_id
LEFT OUTER JOIN groups c ON b.class_id = c.group_id
LEFT OUTER JOIN teacher_groups_subjects d ON c.group_id = d.group_id
WHERE b.student_id IS NULL
Kickstart
  • 21,403
  • 2
  • 21
  • 33
  • Thank you, this works where student_id doesn`t exist. I want to find where group_id is used for a student who`s class_id is different than the one used in the table groups.. can u help me?! – user1799573 Nov 05 '12 at 09:19
  • Bit confused at what you want. While you could do a join similar to the one above, but changing the WHERE clause to check for a null c.group_id, I am not sure that this logically gives you something useful (beyond a list of students who are assigned to a class that doesn't exist) – Kickstart Nov 05 '12 at 09:28
  • It`s complicated.. In students_info I have class_id and student_id. In groups I have class_id and group_id. In teacher_groups_subjects I have class_id, group_id and student id. ,,,, I want to find the students that their class_id in the table students_info is NOT equal to the class_id in the table teacher_groups_subjects?! – user1799573 Nov 05 '12 at 09:53
  • I feel like I am missing something, but if you just want to match up on student id but then check for a mismatch on class SELECT * FROM students_info a INNER JOIN teacher_groups_subjects b ON a.student_id = b.student_id WHERE a.class_id != b.class_id – Kickstart Nov 05 '12 at 10:02