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.
TableC. For simplicity reasons, assume they all have
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.
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) ;
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