Как правильно исключить пользователя из запроса?

Question

[lum1ere]

exame_mark

students

По условию мне нужно выводить только тех у кого есть 4, 5. И если у человека есть 2, то его уже не брать.
Пишу такой код:

select e.mark, course, g.name, s.name, lastname, surname, p.name, f.name from students s
left join groupa g on s.group_id = g.id
inner join exam_mark e on s.id = e.student_id
inner join peciality p on g.peciality_id = p.id
inner join facult f on p.facult_id = f.id
where e.mark >= 4 and e.mark <> 2

А мне выводит человека у которого есть 2 и другие положительные оценки.
В чем суть, по данным которые сейчас есть, нужно чтоб выводило только Александру.

Comments

[lum1ere]

Или просто делать выборку и шаманить в коде?

[ThunderCat]

lum1ere, Вам нужно выбрать всех у кого оценка больше или равна 4? Тогда and e.mark <> 2 лишнее условие. Кроме того, какого типа у вас поле e.mark?

[lum1ere]

ThunderCat,
Александр, тут лишний потому что у него есть одна 2

Answer 1

[Константин Цветков]

-- Только значимые столбцы
CREATE TABLE exam_mark (ID INT IDENTITY, student_id INT, mark INT)
INSERT exam_mark (student_id, mark ) 
  VALUES (4, 5), (4, 4), (4, 4), (4, 5), (4, 2),
         (5, 5), (5, 5), (5, 4), (5, 3), (5, 5),
         (6, 2), (6, 2), (6, 3), (6, 2), (6, 2),
         (7, 3), (7, 3), (7, 3), (7, 3), (7, 3)

SELECT gd.* 
  FROM exam_mark AS gd
    LEFT JOIN exam_mark AS bd ON bd.student_id = gd.student_id AND bd.mark = 2
     -- у кого есть 4, 5               но нет 2
  WHERE (gd.mark IN (4, 5)) AND (bd.student_id IS NULL)

Answer 2

[Руслан .]

Попробуйте так:

select e.mark, course, g.name, s.name, lastname, surname, p.name, f.name from students s
left join groupa g on s.group_id = g.id
inner join exam_mark e on s.id = e.student_id
inner join peciality p on g.peciality_id = p.id
inner join facult f on p.facult_id = f.id
where e.mark >= 4
    and not exists (select 1 
                               from exam_mark e1
                             where e1.student_id = s.id
                                 and e1.mark        <> 2)

Comments

[lum1ere]

Простите, может я что-то не понимаю, но ничего не выводиться

[Константин Цветков]

not exists
e1.mark = 2

Хорошисты без двоек.

Answer 3

[ThunderCat]

lum1ere, на уровне строки у него нет двойки, естественно эти строки будут показаны.
вам нужен груп бай студент_ид и хэвинг мин(е.марк) > 3

Answer 4

[lum1ere]

Всем спасибо за помощь новичку в sql.
Код сейчас работает отлично!

select e.mark, course, g.name, s.name, lastname, surname, p.name, f.name from students s
    left join groupa g on s.group_id = g.id
    inner join exam_mark e on s.id = e.student_id
    inner join peciality p on g.peciality_id = p.id
    inner join facult f on p.facult_id = f.id
    where not exists (
        select 1 from exam_mark e1
                 where e1.student_id = s.id and e1.mark = 2)

Comments

[Константин Цветков]

Добавьте студента с тройками и без двоек — перестанет работать.