CODEWITHSUNDEEP
mysqlsqljoincountunion
Juan Castillo
Juan Castillo

Reputation: 103

Mysql error with UNION query

select t1.tema,
    count(p.id_pregunta) as num_preguntas
from preguntas p
right join subtema s on p.id_subtema = s.id_subtema
join tema t on t.id_tema = s.id_tema
union
select t1.tema,
    count(r1.id_respuesta) as preg_respuesta
from respuestas r1
right join preguntas p1 on r1.id_pregunta = p1.id_pregunta
right join subtema s1 on p1.id_subtema = s1.id_subtema
right join tema t1 on t1.id_tema = s1.id_tema
group by t1.tema
order by preg_respuesta desc

What is wrong with this query? I'm getting this:

error #1054 - Unknown column 'preg_respuesta' in 'order clause''

Please help me.

Upvotes: 1

Views: 245

Answers (3)

japzdivino
japzdivino

Reputation: 1746

This is aliasing problem... put the table alias name in where the column preg_respuesta belongs.

Ex: IF preg_respuesta column is belong to table respuestas which have alias r1. the order by will look something like:

order by r1.preg_respuesta desc

Upvotes: 0

HAYMbl4
HAYMbl4

Reputation: 1470

I think problem in alias for count column. In first query you name it num_preguntas in second preg_respuesta in finel result this column have name by name from first query num_preguntas.

Change orger by preg_respuesta desc to orger by num_preguntas desc

Upvotes: 1

Darwin von Corax
Darwin von Corax

Reputation: 5246

A SELECT ... UNION takes its column names from the first query. Try

... ORDER BY num_preguntas DESC

or

.... ORDER BY 2 DESC

(since you're sorting by the 2nd column in the result.)

Upvotes: 3

Related Questions