sql search filter - exception

Asked

Viewed 47 times

1

Hello!

I’m having a hard time developing a logic for a bank search.

I need to return from the bank a table with the student list enrolled with a certain type of voucher.

But each student can have N vouchers assigned to his registration.

To illustrate, I made this voucher table. All students have the A voucher, but I need a select that returns those who have exclusively the type A. Students 002 and 003 should not enter the search since they have the B and/or C as well.

inserir a descrição da imagem aqui

How can I do that?

My appointment is like this

SELECT P.COD_ALUNO, P.NOME, P.ID_TIPO_PESSOAS 
FROM TB_VOUCHER V
INNER JOIN TB_PESSOAS P ON P.COD_ALUNO = V.COD_ALUNO 
WHERE 
  V.TIPO_VOUCHER = 239 AND 
  V.COD_ALUNO IS NOT NULL AND 
  P.ID_TIPO_PESSOAS = 10
sql database select

  • type in the question the select/from/Where you have done so far

  • Search for vouchers = A and using the NOT EXISTS clause make a subsect for the student with a <> A voucher.

  • edit the question and put the full query there, it is better to view

  • placed the query. are two tables that pick up students with voucher 239 and who are with active enrollment

2 answers

0

Assess if this is what you want:

SELECT P.COD_ALUNO, P.NOME, P.ID_TIPO_PESSOAS 
FROM TB_VOUCHER V
INNER JOIN TB_PESSOAS P ON P.COD_ALUNO = V.COD_ALUNO 
WHERE 
  V.TIPO_VOUCHER = 239 AND 
  V.COD_ALUNO IS NOT NULL AND 
  P.ID_TIPO_PESSOAS = 10 AND
  NOT EXISTS (SELECT 1 FROM TB_VOUCHER AUX WHERE AUX.COD_ALUNO = V.COD_ALUNO AND AUX.TIPO_VOUCHER <> 239)

It doesn’t exactly match your description, but if I interpreted it correctly, it seems to be in accordance with the code posted.

0

Junior, here is a suggestion for tests using the claushula Having with the functions Min and Max:

SELECT P.COD_ALUNO, P.NOME, P.ID_TIPO_PESSOAS 
FROM TB_PESSOAS P
INNER JOIN TB_VOUCHER V ON V.COD_ALUNO = P.COD_ALUNO 
WHERE 
  P.ID_TIPO_PESSOAS = 10 AND
  V.TIPO_VOUCHER = 239
GROUP BY P.COD_ALUNO, P.NOME, P.ID_TIPO_PESSOAS
HAVING
  MIN(V.TIPO_VOUCHER) = 'A' AND
  MAX(V.TIPO_VOUCHER) = 'A'

In cases where only type 'A' exists, the Min and Max functions will return 'A', and if there are other types one of the 2 functions will return a different value.

I hope it helps

Browser other questions tagged sql database select

You are not signed in. Login or sign up in order to post.