Query to select certain columns that meet the condition

Question

Query to select certain columns that meet the condition

Navigation

#1 by (0 votes)

0

I have the following table:

create table prof_est(id_est int, id_prof int, id_curso int, periodo varchar);

id_est corresponds to the student id id_prof is the identification of the teacher who is dictating the subject id_curso is the identification of the course you are taking and finally period corresponds to the year and semester that the student is taking said course with said professor

The question asks me to generate a list that shows the id of students who have always taken more than 2 subjects per period. The information in the table is next .

This means that I should only return the ID of 203 because only this one complies with having taken more than 2 subjects in each period (It only appears in one period), the id of 201 should not appear because it has a period in the who has not fulfilled the condition.

I have this query for the time being

select id_est, periodo, count(periodo) as materias from prof_est
group by id_est, periodo

the table that the query sends me is here what the table shows corresponds to the id of the student, the period in which he has been, and the number of subjects he has taken in that period.

What I am looking for is only the id's of the students that in all the periods they have taken, have taken more than 2 subjects (which would only be the case of 203), as explained before, 201 would not be shown, since There is a period in which he has only taken 1 subject.

sql postgresql

asked by pakoxtror 20.05.2018 в 03:22

source

1 answer

unable to install break point, reason: absent line number information laravel class user not found in Controller and if in Tinker

score 0 · Accepted Answer

In SQL it would do something like this.

DECLARE @CANT_PERIODOS INT
DECLARE @TAB TABLE(IDE INT,CANT_CURSOS INT,PERIODO VARCHAR(20))
DECLARE @TAB2 TABLE(ID_E INT,CANT_PER INT)

SELECT @CANT_PERIODOS=COUNT(periodo) from prof_est
GROUP BY periodo    

INSERT INTO @TAB
SELECT id_est, count(id_curso), periodo from prof_est
GROUP BY id_est, periodo

INSERT INTO @TAB2
SELECT IDE,CANT(PERIODO) FROM @TAB
WHERE CANT_CURSOS>2
GROUP BY IDE

SELECT ID_E FROM @TAB2
WHERE CANT_PER = @CANT_PERIODOS