Limit INNER JOIN query to only one ID

Asked

Viewed 120 times

2

I’m performing the following consultation:

SELECT enquete.image, 
       enquete.status      AS status_enquete, 
       enquete.id          AS id_enquete, 
       enquete.title       AS titulo_enquete, 
       perguntas.id        AS id_pergunta, 
       perguntas.title     AS titulo_pergunta
FROM   obs_survey_questions AS perguntas 
       INNER JOIN obs_survey AS enquete 
               ON enquete.id = perguntas.survey_id 
       INNER JOIN (SELECT id 
                   FROM   obs_survey 
                   GROUP  BY id) b 
               ON perguntas.survey_id = b.id 
WHERE  status = 1 
GROUP  BY id_pergunta

And getting the following result:

inserir a descrição da imagem aqui

However, my goal is that it comes only from ONE ID_ENQUETE, but I don’t know why there’s always two, in the case above, the 14 and the 1.

sql

  • indicates which ID (id_poll) of the poll you want to return only and then make use of a GROUP BY id_poll.

  • But which ID should be returned? Just return one? What is the purpose of the query? You have to go into more detail about your question, otherwise it’s hard to help.

  • @Joãomartins The problem is that all surveys with status 1 are coming, but I only want the first active and their respective questions.

2 answers

2


Try it this way (if I understand what you want):

SELECT      E.image
        ,   E.status    AS status_enquete
        ,   E.id        AS id_enquete
        ,   E.title     AS titulo_enquete
        ,   P.id        AS id_pergunta
        ,   P.title     AS titulo_pergunta
FROM        obs_survey_questions    P 
INNER JOIN  (
                SELECT      TOP 1 image
                        ,   status
                        ,   id
                        ,   title
                FROM        obs_survey
                ORDER BY    id
            )                       E ON E.id = P.survey_id 
GROUP BY    P.id_pergunta

Basically go get the first one ID, ordered by that same column, and together with the table obs_survey_questions.

  • That’s what it was. Perfect.

1

since you did not specify a survey_id in Where you can use Top:

'SELECT TOP 1 enquete.image, 
   enquete.status      AS status_enquete, 
   enquete.id          AS id_enquete, 
   enquete.title       AS titulo_enquete, 
   perguntas.id        AS id_pergunta, 
   perguntas.title     AS titulo_pergunta
FROM   obs_survey_questions AS perguntas 
   INNER JOIN obs_survey AS enquete 
           ON enquete.id = perguntas.survey_id 
   INNER JOIN (SELECT id 
               FROM   obs_survey 
               GROUP  BY id) b 
           ON perguntas.survey_id = b.id 
   WHERE  status = 1 
   GROUP  BY id_pergunta '

  • It didn’t work out, bro

Browser other questions tagged sql

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