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.