DLookUp query to MySql

0
votes

I am working on converting a legacy MS access system to a spring-boot application and I came across a big query. I am mostly done with converting the functions from access to mysql but not able to understand how to convert the following DLookUp sub-query as a mySql subquery

DLookUp("[price]","tbl_so","[so_id] = " & tbl_trade.so_id & " AND [product_id] = " & tbl_po.product_id

What I understood is following won't work as I don't have the Inner Joins set between the 3 tables, tbl_so, tbl_po, tbl_trade

SELECT tbl_so.price FROM tbl_so WHERE tbl_so.so_id = tbl_trade.so_id AND tbl_so.product_id = tbl_po.product_id

My question is how do I know how the tables will join with each other in this case and also when this DLookUp query is seldom used. And also the solution for this query.

1
mysqlvbams-access
your mysql will give you an error you have to inner join the tables first. Th efirst join select all the rows from tabl_so that have a partner in the second table and so on. But as long you got both system running. See what access returns and the see what mysql got you. - nbk

1 Answers

1
votes

Well, as a general rule, dlookup() can be replaced with a left join. However, you can also use a sub-query and they tend to be "less" change to the query.

So, if we had this:

SELECT id, partNum, dlookup("PartDescrt","tblParts","PartID  = " & partNum) 
as Description from tblOrders.

You would replace the above dlookup() with a sub-query like this:

SELECT id, partNum, 
  (select PartDescrt from tblParts where tblParts.PartID = tblOrders.PartNum)
  AS Description
 from tblOrders

The above is SQL or access sql syntax, but quite sure the same should work for MySQL.

If there is more then one partNumber that matches the above, then add a TOP 1, and a order by with some unique row (PK ID column is best). So the query becomes:

  (select TOP 1 PartDescrt from tblParts where tblParts.PartID = tblOrders.PartNum 
     ORDER BY PartDescrt, ID DESC)
  AS Description