subconsultas - Migración de consultas de Firebird a MySQL: seleccione Subconsulta de unión interna

subconsulta sql con mas de una columna

Question

Tengo una consulta que funcionó en nuestro módulo de datos de Firebird SQL.

Migramos a MySQL y todas mis consultas funcionan sin problemas, excepto en este caso.

Por favor ayúdame a arreglar esto. Me sale un error:

No se pudo ejecutar. Columna desconocida ''part.id'' en ''on clause''

Mi consulta de Firebird:

SELECT vendor.name AS "Vendor Name",
Cast(Cast(vendorparts.lastdate AS date) AS CHAR(10)) AS "Last Date",
CASE product.price
WHEN ''0'' THEN ''CONFIRM''
WHEN NULL THEN ''CONFIRM''
ELSE Round(product.price, 2)
end AS "D-Price",
Cast(vendorparts.lastcost AS DECIMAL(18, 2)) AS "Last Cost",
Cast(lowestcost.lowestcost AS DECIMAL(18, 2)) AS "Lowest Cost",
Cast(highestcost.highestcost AS DECIMAL(18, 2)) AS "Highest Cost",
part.num AS "Part Number",
part.description AS "Part Description"
FROM vendor,
vendorparts,
part,
product
INNER JOIN (SELECT vendorparts.partid,
Max(vendorparts.lastcost) AS Highestcost
FROM vendorparts
GROUP BY vendorparts.partid) AS highestcost
ON part.id = highestcost.partid
INNER JOIN (SELECT vendorparts.partid,
Min(vendorparts.lastcost) AS Lowestcost
FROM vendorparts
GROUP BY vendorparts.partid) AS lowestcost
ON part.id = lowestcost.partid
WHERE vendor.id = vendorparts.vendorid
AND product.partid = part.id
AND vendorparts.partid = part.id
AND vendorparts.lastcost <> 0 

Todas las tablas están en la base de datos a excepción del lowestcost y el highestcost que se crearon en las consultas secundarias.

Espero que mi solicitud esté claramente escrita. Pero para resumir, necesito que esta consulta Firebird funcione para migrar a MySQL.

¿Por qué funcionaría esto en Firebird pero no en MySQL?

Answer 1

Esta consulta tampoco funcionaría en Firebird 3.0 y versiones posteriores (ver Soporte para combinaciones de sintaxis mixtas ). La razón es que está combinando combinaciones de estilo SQL-89 con combinaciones de estilo SQL-92.

Necesita volver a escribir la consulta para usar combinaciones explícitas en todas partes, así que:

...
FROM vendor
inner join vendorparts on vendor.id = vendorparts.vendorid
inner join part on vendorparts.partid = part.id
inner join product on product.partid = part.id
INNER JOIN (SELECT vendorparts.partid,
Max(vendorparts.lastcost) AS Highestcost
FROM vendorparts
GROUP BY vendorparts.partid) AS highestcost
ON part.id = highestcost.partid
INNER JOIN (SELECT vendorparts.partid,
Min(vendorparts.lastcost) AS Lowestcost
FROM vendorparts
GROUP BY vendorparts.partid) AS lowestcost
ON part.id = lowestcost.partid
WHERE vendorparts.lastcost <> 0 

Answer 2

No mezcle combinaciones explícitas e implícitas
Evite el uso del mismo alias en los nombres de columnas y tablas (en este ejemplo ai se refieren a t1 y t2) y evite el AS para el nombre de la tabla de subselección

SELECT
vendor.name AS "Vendor Name",
Cast(Cast(vendorparts.lastdate AS date) AS CHAR(10)) AS "Last Date",
CASE product.price
WHEN ''0'' THEN ''CONFIRM''
WHEN NULL THEN ''CONFIRM''
ELSE Round(product.price, 2)
end AS "D-Price",
Cast(vendorparts.lastcost AS DECIMAL(18, 2)) AS "Last Cost",
Cast(lowestcost.lowestcost AS DECIMAL(18, 2)) AS "Lowest Cost",
Cast(highestcost.highestcost AS DECIMAL(18, 2)) AS "Highest Cost",
part.num AS "Part Number",
part.description AS "Part Description"
FROM vendor
INNER JOIN vendorparts on vendor.id = vendorparts.vendorid AND vendorparts.lastcost <> 0
INNER JOIN part on vendorparts.partid = part.id and
INNER JOIN product on product.partid = part.id
INNER JOIN (SELECT vendorparts.partid,
Max(vendorparts.lastcost) AS Highestcost
FROM vendorparts
GROUP BY vendorparts.partid) t1
ON part.id = t1.partid
INNER JOIN (SELECT vendorparts.partid,
Min(vendorparts.lastcost) AS Lowestcost
FROM vendorparts
GROUP BY vendorparts.partid) t2
ON part.id = t2.partid

Answer 3

El problema es que en mySQL el operador de coma tiene una precedencia menor que el operador de join , por lo tanto, la product inner join (subquery) on part.id = highestcost.partid inner join (subquery) on part.id = lowestcost.partid del product inner join (subquery) on part.id = highestcost.partid inner join (subquery) on part.id = lowestcost.partid se evalúa antes la tabla de part se une en la expresión, de ahí el mensaje de error.

Reemplace los operadores de coma con operadores de join simple y mueva las condiciones de unión de cláusula where cláusulas on y todo estará bien:

...
FROM vendor
inner join vendorparts on vendor.id = vendorparts.vendorid
inner join part on vendorparts.partid = part.id
inner join product on product.partid = part.id
INNER JOIN (SELECT vendorparts.partid,
Max(vendorparts.lastcost) AS Highestcost
FROM vendorparts
GROUP BY vendorparts.partid) AS highestcost
ON part.id = highestcost.partid
INNER JOIN (SELECT vendorparts.partid,
Min(vendorparts.lastcost) AS Lowestcost
FROM vendorparts
GROUP BY vendorparts.partid) AS lowestcost
ON part.id = lowestcost.partid
WHERE vendorparts.lastcost <> 0 

Si tiene más consultas de este tipo en las que mezcla el operador de coma y las combinaciones explícitas, debe verificarlas, ya que pueden producir resultados diferentes incluso si no hubiera un error de sintaxis en MySQL.