Jump to content

Mysql query problem with data retrieved


gmc1103
 Share

Recommended Posts

I'm trying to run a query but my outcome is not what i need. So the problem is: A user can be diretor and if this is the case he can see all activities from his department, and he can be user of another department too, in this case not director but only user. I have 8 departments each with one director, so the following query should give me the activities of the department and the activities of this particular user in other department:

SELECT t1.idAtividade,
       t1.idProfessor,
       t2.Escola,
       t1.Atividade,
       t1.Periodo,
       t1.Mes,
       t1.haveClasses,
       t1.DataPrevista,
       t1.Destinatarios,
       t1.Orcamento,
       t1.PdfAtividade,
       t1.Avaliacao,
       t1.PdfAvaliacao,
       t1.idProfessor,
       p.Nome,
       d.Departamento,
       p2.Projeto,
       t1.idProjeto
FROM atividades AS t1
         INNER JOIN professores p on t1.idProfessor = p.idProfessor
         RIGHT JOIN atividadesgrupos ag on t1.idAtividade = ag.idAtividade
         RIGHT JOIN departamentosatividades da on t1.idAtividade = da.idAtividade
         RIGHT JOIN departamentos d on da.idDepartamento = d.idDepartamento
         INNER JOIN escolas AS t2 ON (t2.idEscola = t1.idEscola)
         INNER JOIN anosescolares AS ae ON (t1.idAnoEscolar = ae.idAnoEscolar)
         INNER JOIN projetos p2 on t1.idProjeto = p2.idProjeto
WHERE ae.Estado = 1 AND da.idDepartamento = 8
ORDER BY (t1.idProfessor = 60) DESC, t1.idProfessor;

 

This query is not working because the department have 22 activities but this user (idProfessor) has 5 in this department and 14 more in another department (defined by idGrupo)

I think i will need a subquery right?

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.