I'm trying to fill a table with the results of a query, however, the results of said query depends on the result of other queries and then it ends up having several thousand selects making the process really long. I was wondering if what i'm trying to achieve is possible through a single query.
What i want to do is this.
fields: storeid, store_name
fields: codproduct, product_name, price1, price2
fields: pricelistid, codproduct, storeid, price1, price2
I'm keeping base prices in the product record itself and custom prices per store in a the table called "PriceList". i get the storeid from the user that is logged in.
I'm currently running a query to let me know if there is a custom price from where the user is logged on, if there is none, then i use the base prices in the "productos" table. But i'm running several thousands of selects each time.
Is there a way to acchieve that in a single query?
the final query needs to return: codproduct, product_name, price1, price2
price1 and price2 being the base price from "product" if there is no custom price in the "PriceList" table.