CODEWITHSUNDEEP
mysqlsql
igx
igx

Reputation: 4231

optimise query with calculate field from several tables

I have four tables 

store[store_id(pk),name]
itemsA(item_id(pk),store_id,name)
itemB(item_id(pk),store_id,name)
itemC(item_id(pk),store_id,name)

I want a query to retrieve a store and the number of items that he have. something like :

select s.store_id ,s.name,count() as numberOfItems from store limit 100

what is the optimal query to achieve that with the following restraints : cannot create a function in the db cannot create view I can only run queries on the db Thanks

Upvotes: 0

Views: 45

Answers (3)

wvdz
wvdz

Reputation: 16641

Stores with no items will not show up with this query. If this is a requirement it will have to be tweaked somewhat.

SELECT s.store_id, COUNT(*)
FROM Store s
JOIN ItemA a ON a.store_id = s.store_id
JOIN ItemB b ON b.store_id = s.store_id
JOIN ItemC c ON c.store_id = s.store_id
GROUP BY s.store_id

A simple modification to also include stores with 0 items:

SELECT s.store_id, COUNT(a.store_id) + COUNT(b.store_id) + COUNT(c.store_id)
FROM Store s
LEFT JOIN ItemA a ON a.store_id = s.store_id
LEFT JOIN ItemB b ON b.store_id = s.store_id
LEFT JOIN ItemC c ON c.store_id = s.store_id
GROUP BY s.store_id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I would recommend doing this with correlated subqueries:

select s.store_id, s.name,
       ((select count(*) from itemsA a where a.store_id = s.store_id) +
        (select count(*) from itemsB b where b.store_id = s.store_id) +
        (select count(*) from itemsC c where c.store_id = s.store_id)
       ) as numberOfItems
from store s
limit 100;

You then want an index in each of the item tables: itemsA(stored_id), itemsB(store_id), and itemsC(store_id).

The reason this is optimized is because it only has to calculate the values for the arbitrary 100 stores chosen by the limit. And, the calculation can be done directly from the index. Other approaches will require doing the calculation for all the stores.

Note: usually when using limit you want an order by clause.

Upvotes: 1

SubqueryCrunch
SubqueryCrunch

Reputation: 1495

If i understood you correctly

DECLARE @store TABLE (store_id INT, name NVARCHAR(100))
DECLARE @itemsA TABLE (item_id INT,store_id INT, name NVARCHAR(100))
DECLARE @itemsB TABLE (item_id INT,store_id INT, name NVARCHAR(100))
DECLARE @itemsC TABLE (item_id INT,store_id INT, name NVARCHAR(100))

INSERT INTO @store VALUES (1,'Store1')
INSERT INTO @store VALUES (2,'Store2')

INSERT INTO @itemsA VALUES (1,1,'itemsA_item1')
INSERT INTO @itemsA VALUES (2,1,'itemsA_item2')
INSERT INTO @itemsA VALUES (3,1,'itemsA_item3')

INSERT INTO @itemsB VALUES (1,2,'itemsB_item1')
INSERT INTO @itemsB VALUES (2,2,'itemsB_item2')
INSERT INTO @itemsB VALUES (3,2,'itemsB_item3')
INSERT INTO @itemsB VALUES (4,1,'itemsB_item4')


INSERT INTO @itemsC VALUES (1,3,'itemsC_item1')
INSERT INTO @itemsC VALUES (2,3,'itemsC_item2')
INSERT INTO @itemsC VALUES (3,2,'itemsC_item3')

SELECT TOP 100 store_id, SUM(HasItems) AS TotalItems FROM
(
    SELECT store_id, COUNT(name) AS HasItems FROM @itemsA GROUP BY store_id
    UNION
    SELECT store_id, COUNT(name) AS HasItems FROM @itemsB GROUP BY store_id
    UNION
    SELECT store_id, COUNT(name) AS HasItems FROM @itemsC GROUP BY store_id
) AS StoreItems
GROUP BY store_id

Upvotes: 0

Related Questions