I have the following query
SELECT preguntas.id,
preguntas.id_tipo_respuesta,
group_concat(opc_multiples.opciones order by opc_multiples.opciones asc) AS Opciones
from opc_multiples,
preguntas,
cat_productos
where preguntas.id_tipo_respuesta = 3
and opc_multiples.id_pregunta = preguntas.id
and preguntas.id_prod_asign = cat_productos.id
group by opc_multiples.id_pregunta
order by cat_productos.nombre_producto asc
I have this second query
SELECT preguntas.id,
preguntas.pregunta,
cat_productos.nombre_producto,
preguntas.id_tipo_respuesta
FROM preguntas,
cat_productos
where preguntas.id_prod_asign = cat_productos.id;
What I have tried and have not achieved is the query so that the As Options of the first query can display everything in the second only when applied, I have tried with join but I have not succeeded.
This way you would do
JOIN
between the different tables.Consider always using explicit JOINs, because they make queries more clear. I think you were having a hard time getting the JOINs out because you were using implicit (based on
WHERE
) JOINs.Here I have used short aliases for the tables, so that the query is more readable. And if you notice I have added to
GROUP BY
the columns that could be repeated, to give a standard FULL GROUP BY to your query, something that MySQL generally ignores and that is not a good practice.I don't have a dataset to test the query with, but it should work like this. If there is any problem tell me in comments.
I'm left with this:
I can understand this definition in the following way:
This (I think) answers what you're asking, but I understand it's an unnecessarily complex query.