PostgreSQL - Consultas complejas

   
Vista:

Consultas complejas

Publicado por Beatleos (2 intervenciones) el 02/09/2009 16:56:02
SELECT forum_id, forum_project, forum_description, forum_owner, forum_name,forum_moderated, forum_create_date,
forum_last_date,sum(case c.message_parent=-1 when true then 1 when false then 0 ELSE null END) as forum_topics,
sum(case c.message_parent >0 when true then 1 when false then 0 ELSE null END) as forum_replies,
user_username, project_name, project_color_identifier, (contact_first_name ||','||contact_last_name) AS owner_name,
SUBSTRING(l.message_body from 1 for 30) AS message_body,LENGTH(l.message_body) AS message_length, watch_user, l.message_parent,
l.message_id,count(distinct v.visit_message) as visit_count, count(distinct c.message_id) as message_count
FROM "projects" AS pr,"users" AS u,"forums" AS forums
LEFT JOIN "forum_messages" AS l ON l.message_id = forum_last_id
LEFT JOIN "forum_messages" AS c ON c.message_forum = forum_id
LEFT JOIN "forum_watch" AS w ON watch_user = 1 AND watch_forum = forum_id
LEFT JOIN "forum_visits" AS v ON visit_user = 1 AND visit_forum = forum_id and visit_message = c.message_id
LEFT JOIN "contacts" AS cts ON contact_id = u.user_contact
LEFT JOIN "companies" AS co ON co.company_id = pr.project_company
LEFT JOIN "project_departments" AS project_departments ON pr.project_id = project_departments.project_id
LEFT JOIN "departments" AS dep ON dep.dept_id=project_departments.department_id
WHERE user_id = forum_owner AND pr.project_id = forum_project AND project_active = 1
GROUP BY forum_id,forum_project,forum_description,forum_owner, forum_name,forum_moderated, forum_create_date, user_username, project_name, project_color_identifier,
forum_last_date
ORDER BY forum_name asc

Al correrlo me da el siguiente error:

ERROR: invalid reference to FROM-clause entry for table "u"
HINT: There is an entry for table "u", but it cannot be referenced from this part of the query.

Cómo soluciono este problema???
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder

RE:Consultas complejas

Publicado por Beatleos (2 intervenciones) el 03/09/2009 22:21:25
"Este error es por usar unos 'joins' definidos con comas y otros con la sentencia JOIN en la misma consulta.

Segun el estandar SQL la palabra JOIN tiene mayor prioridad que la coma cuando se definen joins y se evalua internamente la consulta. Por ello, el alias 'u' que defines con '"users" AS u' no esta disponible en el 'ON contact_id = u.user_contact'.

No definas joins de dos maneras diferentes en la misma consulta. Define '"projects" AS pr,"users" AS u,"forums" AS forums' con sentencias JOIN.

--
Rafael Martinez"
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar