unir - Subconsulta correlacionada con MySQL en la sintaxis JOIN

unir dos campos en sql

Question

Me gustaría proporcionar una condición WHERE en una consulta interna especificando innertable.id = outertable.id. Sin embargo, MySQL (5.0.45) informa "Columna desconocida ''outertable.id'' en ''where clause''". ¿Este tipo de consulta es posible?

La consulta interna está girando filas a columnas usando un GROUP BY. Esto podría realizarse completamente en la consulta externa, pero posiblemente incurriría en gastos adicionales debido a las combinaciones adicionales.

Alternativamente, puedo dejar la condición WHERE en la consulta interna y en su lugar especificar un ON outertable.id = innerquery.id, pero luego obtendría todo el conjunto de filas de consultas internas para unir de nuevo el exterior, que es ineficiente.

El SQL actual aparece a continuación:

select t.ticketid, u.userid, t.fullname, u.loginapi_userid, t.email, tp.subject, tp.contents, a.PhoneNumber, a.Location, a.Extension, a.BusinessUnit, a.Department
from swtickets t
inner join swticketposts tp on t.ticketid = tp.ticketid
inner join swusers u on t.userid = u.userid
left join
  (
  select
  cfv.typeid,
  min(case cfv.customfieldid when 1 then cfv.fieldvalue end) as ''PhoneNumber'',
  min(case cfv.customfieldid when 3 then cfv.fieldvalue end) as ''Location'',
  min(case cfv.customfieldid when 5 then cfv.fieldvalue end) as ''Extension'',
  min(case cfv.customfieldid when 8 then cfv.fieldvalue end) as ''BusinessUnit'',
  min(case cfv.customfieldid when 9 then cfv.fieldvalue end) as ''Department''
  from swcustomfieldvalues cfv
  where cfv.typeid = t.ticketid
  group by cfv.typeid
  ) as a on 1 = 1
where t.ticketid = 2458;

Answer 1

Me imagino que el problema es ''cfv.typeid = t.ticketid'', ¿entonces? Mi opinión al respecto sería que, aunque MySQL admite subconsultas correlacionadas, lo que está intentando hacer parece que podría fallar en una unión porque la consulta ''interna'' no está realmente ''dentro'' del resto de la consulta como si fuera en una cláusula WHERE. Pero parece que podría sacar la cláusula where de la subconsulta y hacer que su condición de unión sea a.typeid = t.ticketid.

Answer 2

Mi sugerencia iba a ser lo que descartabas por razones de eficiencia. Por ejemplo, omitir la cláusula where y usar una combinación (según t.ticketid = a.ticketid)

¿Has podido demostrar tus pensamientos sobre la ineficiencia mediante algunos ejemplos concretos? Sé lo que dices, pero cualquiera que sea el método que uses en cada fila de la consulta externa se está uniendo a cada fila en la consulta interna, por lo que dependiendo del plan de ejecución puede que no sea tan ineficiente como sospechas.

Answer 3

Está utilizando el diseño Entity-Attribute-Value y, en última instancia, no hay forma de hacerlo escalable si intenta generar conjuntos de resultados convencionales. No intente hacer esto en una consulta.

En cambio, consulte primero sus tablas normalizadas:

SELECT t.ticketid, u.userid, t.fullname, u.loginapi_userid, t.email, 
  tp.subject, tp.contents
FROM swtickets t
 INNER JOIN swticketposts tp ON (t.ticketid = tp.ticketid)
 INNER JOIN swusers u ON (t.userid = u.userid)
WHERE t.ticketid = 2458;

A continuación, consulte sus campos personalizados, con el resultado en varias filas del conjunto de resultados:

SELECT cfv.customfieldid, cfv.fieldvalue
FROM swcustomfieldvalues cfv
WHERE cfv.typeid = 2458;

Obtendrá varias filas en el conjunto de resultados, una fila para cada campo personalizado:

+---------------+--------------+
| customfieldid | fieldvalue   |
+---------------+--------------+
|             1 | 415-555-1234 |
|             3 | Third office |
|             5 | 123          |
|             8 | Support      |
|             9 | Engineering  |
+---------------+--------------+

Luego debe escribir el código de la aplicación para asignar los campos del conjunto de resultados a los campos del objeto de la aplicación, en un bucle.

El uso de una tabla Entity-Attribute-Value de esta manera es más escalable tanto en términos de rendimiento como de mantenimiento del código.

Answer 4

Lo escribiría con varias combinaciones. Cuando dice que "posiblemente incurriría en gastos generales adicionales", me dice que no lo ha probado para estar seguro. Si tiene índices decentes, las uniones deberían ser bastante triviales.

Esto también muestra solo una de las trampas del patrón genérico de diseño de mesa "mantener todo".

Answer 5

La respuesta a su pregunta es no, no es posible hacer referencia a nombres de correlación mientras lo hace. La tabla derivada es producida por su consulta interna antes de que la consulta externa comience a evaluar las uniones. Por lo tanto, los nombres de correlación como t , tp y u no están disponibles para la consulta interna.

Para resolver esto, recomendaría usar el mismo valor entero constante en la consulta interna, y luego unirme a la tabla derivada en la consulta externa usando una condición real en lugar de 1=1 .

SELECT t.ticketid, u.userid, t.fullname, u.loginapi_userid, t.email,
  tp.subject, tp.contents, a.PhoneNumber, a.Location, a.Extension,
  a.BusinessUnit, a.Department
FROM swtickets t
 INNER JOIN swticketposts tp ON (t.ticketid = tp.ticketid)
 INNER JOIN swusers u ON (t.userid = u.userid)
 LEFT OUTER JOIN (
  SELECT cfv.typeid,
    MIN(CASE cfv.customfieldid WHEN 1 THEN cfv.fieldvalue END) AS ''PhoneNumber'',
    MIN(CASE cfv.customfieldid WHEN 3 THEN cfv.fieldvalue END) AS ''Location'',
    MIN(CASE cfv.customfieldid WHEN 5 THEN cfv.fieldvalue END) AS ''Extension'',
    MIN(CASE cfv.customfieldid WHEN 8 THEN cfv.fieldvalue END) AS ''BusinessUnit'',
    MIN(CASE cfv.customfieldid WHEN 9 THEN cfv.fieldvalue END) AS ''Department''
  FROM swcustomfieldvalues cfv
  WHERE cfv.typeid = 2458
  GROUP BY cfv.typeid
  ) AS a ON (a.typeid = t.ticketid)
WHERE t.ticketid = 2458;