Query with Join returning empty

Asked

Viewed 37 times

0

Please help me check my query because it is giving an error that I am not able to identify. If I do the simple consultation:

 SELECT 
    *
       FROM 
         respostas 
                   WHERE 
                     resp_usuario='8'
                    AND  resp_questionario='6'

Returns results normally,more if I enter a Join does not return anything,follow the Join query:

SELECT 
     q.quest_nome                    AS QUESTIONARIO,
     u.usu_id                        AS ID_USUARIO,
     u.usu_nome                      AS USUARIO,
     l.loja_id                       AS END,
     a.area_desc                     AS SETOR,
     sb.sub_area_nome                AS SUBAREA,
     p.perg_desc                     AS PERGUNTA,
     r.resp_resposta                 AS RESPOSTA,
     p.perg_peso                     AS PESO,
     (p.perg_peso * r.resp_resposta) AS PONDERADA,
     (p.perg_peso * 5)               AS MAXIMO,
     (p.perg_peso * r.resp_resposta / (p.perg_peso * 5)*100)PERCENTUAL
       FROM 
         respostas AS r
         INNER JOIN questionario AS q  ON r.resp_questionario = q.quest_id
         INNER JOIN usuarios     AS u  ON r.resp_usuario      = u.usu_id
         INNER JOIN lojas        AS l  ON r.resp_loja         = l.loja_id
         INNER JOIN area         AS a  ON r.resp_area         = a.area_id
         INNER JOIN sub_area     AS sb ON r.resp_subgrupo     = sb.sub_area_cod
         INNER JOIN perguntas    AS p  ON r.resp_pergunta_id  = p.perg_id
                     WHERE 
                       r.resp_usuario='8'
                       AND r.resp_questionario='6'

What I could be doing wrong ?

Att,

mysql database
  • 2

    Most likely, no record meets the conditions. Tip can go removing the Join one by one and find out which 'fault' query piece or exchange it for left Join just for testing.

  • 1

    Great, I managed to identify by the left Join. Thank you very much, please put as an answer so I can score for you. Hugging.

1 answer

0


I was able to reach the result as directed by @rray .

Thank you. 

SELECT 
         q.quest_nome                    AS QUESTIONARIO,
         u.usu_id                        AS ID_USUARIO,
         u.usu_nome                      AS USUARIO,
         l.loja_id                       AS END,
         a.area_desc                     AS SETOR,
         sb.sub_area_nome                AS SUBAREA,
         p.perg_desc                     AS PERGUNTA,
         r.resp_resposta                 AS RESPOSTA,
         p.perg_peso                     AS PESO,
         (p.perg_peso * r.resp_resposta) AS PONDERADA,
         (p.perg_peso * 5)               AS MAXIMO,
         (p.perg_peso * r.resp_resposta / (p.perg_peso * 5)*100)PERCENTUAL
           FROM 
             respostas AS r
             LEFT JOIN questionario AS q  ON r.resp_questionario = q.quest_id
             LEFT JOIN usuarios     AS u  ON r.resp_usuario      = u.usu_id
             LEFT JOIN lojas        AS l  ON r.resp_loja         = l.loja_id
             LEFT JOIN area         AS a  ON r.resp_area         = a.area_id
             LEFT JOIN sub_area     AS sb ON r.resp_subgrupo     = sb.sub_area_cod
             LEFT JOIN perguntas    AS p  ON r.resp_pergunta_id  = p.perg_id
                         WHERE 
                           r.resp_usuario='8'
                           AND r.resp_questionario='6'

Browser other questions tagged mysql database

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