Problem with UNION ALL

Question

Problem with UNION ALL

Navigation

#1 by (3 votes)

1

I have a problem with a SQL statement that I do not use too much, in fact this is the first time I use it for practical purposes.

I have two tables, one of questions and one of 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. That is, each question corresponds to a single 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 sql

asked by MNibor 16.11.2017 в 18:33

source

1 answer

ignore if confirm is canceled How to call from a constructor to another Java Constructor

score 3 · Accepted Answer

To do what you want, in the query of the answer you must specify the idPregunta that is related to idNota by means of 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 of all the answers and you pasted the information of a unique answer, reason why of equal the answer must have its respective condition.