0
I am making a Join between tables, where I need to return all the rows of the first table, however I use a Where to limit in the field of the date, so I end up limiting my result to only the lines that belong to that specific date, thus making n return all rows of the first table. In case, all rows of the first table that are not on that date, must return 0, someone give me a light on how to do ?
Obs: I’m already using left Outer Join to get all the data from the first table.
select aa.nome_do_curso, count(cc.alunos_matriculados) contagem
from curso aa
left outer join id_aluno bb on aa.curso_ident = bb.curso_ident
left outer join matricula cc on bb.curso_registro = cc.curso_registro
left outer join periodo dd on cc.periodo_a = dd.periodo_a
where dd.data like '2006' or dd.data like '2007'
group by aa.nome_do_curso
order by 'contagem' desc
where date = scan
You could edit your question and put your query?
– Fernando Souza
Share the Query you have. Just edit and add.
– EmanuelF
You are taking all values from the second table while doing
right join– Jefferson Quesado
To display the line you have to use a subquery instead of a Where
– Wilson Faustino
I believe the question is duplicated, maybe this answer will help: https://answall.com/questions/6441/qual-%C3%A9-a-difference%C3%A7a-entre-Inner-Join-e-outer-Join
– Lucas de Souza Cruz
Bro, change your question and post your query codes
– Fabricio
I changed it, it was a bit buggy there at the time of posting, but I hope you understand :[
– Mahatt
datacomes from which table?– Jefferson Quesado
date comes from the table period
– Mahatt
(First that your SQL does not run) E
alunos_matriculadosis not already the sum of the amount of students of the course? Or which table belongs to this field?– Don't Panic
Ta a little strange to your query. You want the total number of students enrolled in each course in the years 2006 and 2007?
– EmanuelF
Enrolled students come from which table? it would be nice to put the prefixes in the select fields, for our understanding and also for their future maintenance. Is your date field text? (char or varchar or something from)
– Felipe Grossi
That Emanuel, I want students enrolled in 2006 and 2007 and if you have a course that has no student, that returns 0 in the research I’m doing
– Mahatt
@Mahatt has tried to use only
left join– EmanuelF
@Mahatt tries this query:
SELECT aa.nome_do_curso, 
 dd.data,
 Count(cc.alunos_matriculados) AS contagem 
FROM curso aa 
 LEFT JOIN id_aluno bb 
 ON aa.curso_ident = bb.curso_ident 
 LEFT JOIN matricula cc 
 ON bb.curso_registro = cc.curso_registro 
 LEFT JOIN periodo dd 
 ON cc.periodo_a = dd.periodo_a 
WHERE (dd.data LIKE '2006' OR dd.data LIKE '2007' )
GROUP BY aa.nome_do_curso 
ORDER BY contagem DESC– EmanuelF