1

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.

Table: Store
fields: storeid, store_name

Table: Product   
fields: codproduct, product_name, price1, price2

Table: PriceList 
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.

1

If I have interpreted correctly your question, here is a query that produces the result given the storeid X:

SELECT codproduct, 
       product_name, 
       COALESCE(l.price1, p.price1) AS price1, 
       COALESCE(l.price2, p.price2) AS price2
FROM Product p LEFT JOIN PriceList l ON p.codproduct = l.codproduct
WHERE storeid = X
2
  • That is super close except that there is a problem. If there are no records of said store for X product in PriceList then it doesn't return anything at all instead of returning p.price. Besides that it works how i want it so i'm just going to work around that. Thank you. – Hikaros Apr 2 '16 at 8:40
  • If there is no PriceList for a certain product then the left join should give null for all the fields of PriceList, so thereis always the price of the product. – Renzo Apr 2 '16 at 10:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.