Combining three SQL queries into one to find a data with reference to a given table order

I understand I couldn’t come up with the right title for this question. Perhaps I will edit it after some inputs from community members.

I have TableA, TableB, TableC. For simplicity reasons, assume they all have ProductID and Price as fields.

In the code, first TableA is searched. If it contains the price for the productid, return. If not, search TableB for the same and then TableC for the same. In many cases, it results in three separate queries. SQL experts is there a way to run just one query with a hierarchical preference. Meaning if the price is found in TableA disregard the results from the remaining two tables, then the second option is to accept the price from TableB if the price doesn’t exist in TableA. Meaning, if all three table contains the price, take the price from TableA. If the price doesn’t exist in TableA take it from TableB and finally from TableC that will always has the price.

Add Comment
2 Answer(s)

One way to approach this uses filtering:

select a.productid, a.price, 'a' as which from a union all select b.productid, b.price, 'b' as which from b where not exists (select 1 from a where a.productid = b.productid) union all select c.productid, c.price, 'c' as which from c where not exists (select 1 from a where a.productid = c.productid) and       not exists (select 1 from b where b.productid = c.productid) ;     
Answered on September 1, 2020.
Add Comment

You can use below query. You need to provide specific ProductID in the @SearchProductID variable.Like now I use @SearchProductID :=1.

 SELECT * FROM  (SELECT IF(A.ProductID =  @SearchProductID && @price<1 , @price := A.Price, IF(B.ProductID = @SearchProductID && @price<1, @price := B.Price, IF(C.ProductID = @SearchProductID && @price<1, @price := C.Price,@price=0 ))) AS Rprice FROM  TableA A ,TableB B,TableC C , (SELECT @price :=0 , @SearchProductID :=1) C) R WHERE R.Rprice>0 
Answered on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.