Home / Questions / 117966
Accepted
MNibor
Asked: 2020-11-17 09:33:34 +0800 CST 2020-11-17 09:33:34 +0800 CST

Problem with UNION ALL

  • 772

I have a problem with an SQL statement that I don't use much, in fact this is the first time I use it for practical purposes.

I have two tables, one for questions and one for answers. The structures are these:

questions:

idpregunta / idnota / fecha / detalle (la pregunta en si) /idusuario

answers:

idrespuesta / idpregunta / fecha / detalle (de la respuesta) / idusuario

The functionality is this: for a note, there are unique questions and answers. In other words, each question has only one answer. I do the following:

 SELECT idpregunta, fecha, detalle FROM preguntas WHERE idnota = 1 
 UNION ALL 
 SELECT idpregunta, fecha, detalle FROM respuestas 
 ORDER BY idpregunta DESC, fecha ASC

Now... in questions I have

idpregunta      idnota          fecha       detalle

1                1              xxxx        pregunta 1

2                1              xxxx        pregunta 2

3                2              ccccc       pregunta 3

and in answers I have

idrespuesta        idpregunta      fecha       detalle

1                       1          xxxxx       respuesta 1

when I do that query it shows me for note 1: question 1 ----> answer 1

but when I access note 2 it shows me: question 2 ----------------> answer 1

I'm doing something wrong!!!

Can you give me a hand ?

mysql

1 Answers

  1. Best Answer

    To do what you want, in the response query you must specify the idPreguntaone that is related to idNotathrough a subquery:

    SELECT 
    idpregunta, fecha, detalle
FROM preguntas
WHERE idnota = 1

UNION ALL

SELECT 
    idpregunta, fecha, detalle
FROM respuestas
WHERE idpregunta = (
        SELECT idpreguntaFROM preguntas
        WHERE idnota = 1
        )
ORDER BY idpregunta DESC, fecha ASC

    Previously, you obtained information from all the responses and pasted the information from a single response, so the response must still have its respective condition.

    • 3