CODEWITHSUNDEEP
mysqlselectgroup-byunion
valicu2000
valicu2000

Reputation: 431

Select all values that are common between EACH 2 tables on several tables

I'm stuck here ... I have 6 tables on the same format: sku, price, stock.
I can't figure out a query to get all the results that are common to at least 2 tables (based on sku as PK) and then apply the rule: get the smallest price where stock>0, or if all the stock=0 take the smallest price.

LE: let's say t1 and t2 have some records in common, t2 and t6 have some records in common, what I want is to gather all those results and then filter them by the rule above. I've already built a PHP script that copes with the above situation but it takes about 10 seconds on a highly indexed database. We're talking here about 50.000 records distributed on different tables and a quite powerful VPS ... my PHP script takes about 250MB of RAM and about 10s to complete. The problem is that if there is a lot of traffic on the server, the script fails and returns a 500 server error. I need to make it faster and I think that I have to redesign the queries just to save resources.

Thanks a lot for any idea!

Upvotes: 0

Views: 88

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Use a union all to bring the tables together and then aggregate:

select sku,
       (case when sum(stock > 0) > 0
             then min(case when stock > 0 then price end)
             else min(price)
        end) as price
from (select sku, price, stock, 'table1' as which from table1 union all
      select sku, price, stock, 'table2' as which from table2 union all
      select sku, price, stock, 'table3' as which from table3 union all
      select sku, price, stock, 'table4' as which from table4 union all
      select sku, price, stock, 'table5' as which from table5 union all
      select sku, price, stock, 'table6' as which from table6
     ) t
group by sku
having count(distinct which) >= 2

The "fancy" logic for the price should implement the logic in your question.

EDIT:

If you want the stock for the min price, I think the following does this:

select sku,
       (case when sum(stock > 0) > 0
             then min(case when stock > 0 then price end)
             else min(price)
        end) as price,
       substring_index(group_concat(stock order by (stock > 0) desc, price), ',', 1) as stock

Upvotes: 1

Related Questions