Migrate Queries to Firebird for MySQL - Select Internal Attach Subquery
I have a query that worked in our SQL Firebird data module.
We switched to MySQL and all my queries are not working except this one.
Please help me fix this. I am getting the error:
Failed to execute. Unknown column "part.id" in the "on" section
My Firebird request:
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
All tables are in the database, except for lowestcost
and highestcost
, which were created in subqueries.
I hope my request is clearly written. But to summarize - I need this working Firebird query to migrate to MySQL.
Why does this work in Firebird but not MySQL?
source to share
The problem is that in mySQL the operator has a lower precedence than the operator join
, so joins are evaluated product inner join (subquery) on part.id = highestcost.partid inner join (subquery) on part.id = lowestcost.partid
before the table is part
joined in the expression, hence the error message.
Replace the comma operators with simple operators join
and move the join conditions from clause where
to clauses on
and you should be fine:
...
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
If you have more queries like this where you mix comma operator and explicit joins, you should check them out because they may give different results, even if there was no syntax error in MySQL.
source to share
This query will also not work in Firebird 3.0 and above (see Mixed Syntax Support. The reason is that you are combining SQL-89 style joins with SQL-92 style joins.
You need to rewrite your query to use explicit joins everywhere, so:
...
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
source to share
Do not mix explicit and implicit join
Avoid using the same alias in column and table names (in this example ai refer to t1 and t2) and avoid AS for table subsection name
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
source to share