0

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,
    g.Grupo,
    d.Departamento,
    p2.Projeto,
    t1.idProjeto
    FROM atividades AS t1
    INNER JOIN professores p on t1.idProfessor = p.idProfessor
    INNER JOIN atividadesgrupos ag on t1.idAtividade = ag.idAtividade
    INNER JOIN grupos g on ag.idGrupo = g.idGrupo
    INNER JOIN departamentosatividades da on t1.idAtividade = da.idAtividade
    INNER 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 = :id_dpt and ag.idGrupo = :idGrupo)
    ORDER BY (t1.idProfessor = :idProfessor) 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?

gmc1103
  • 11
  • 3
  • In your where clause: `:id_dpt`, `:idGrupo` and `:idProfessor` are variables? otherwise you should use `g.idGrupo` like in your `INNER JOIN`. It would help if you can show up the `INT, VARCHARS, other data types And show what the variables are they replaced with your search content`. Your SQL-Query-End looks a little bit unregular: `DESC, t1.idProfessor;");` - I mean no open double quotes. – MitnickCodeHelper May 27 '21 at 11:23
  • yest..they are variables.... they are both INT – gmc1103 May 27 '21 at 13:00
  • okay, is your sql query inner **php, python or some else programmer language**? – MitnickCodeHelper May 27 '21 at 13:08
  • It's for php...Thanks – gmc1103 May 27 '21 at 15:15
  • I found a hint - give it a try: `` [PHP-Error](https://stackify.com/display-php-errors/) [MySQL-Error](https://stackoverflow.com/questions/12227626/how-do-i-display-a-mysql-error-in-php-for-a-long-query-that-depends-on-the-user) *NOTE: don't use this in a production environment. – MitnickCodeHelper May 27 '21 at 20:22

0 Answers0