mySQL JOIN three tables - Ecommerce items , views and sales

Question

I am building an Ecommerce system using php mySql, and I don't know how to LEFT JOIN three tables.

I need a list from all store items adding how many views and sales each item has

            TABLE #1 - store_sales
            ---------------
            itemID | date
            --------------

            TABLE #2 - store_views
            --------------
            itemID | date
            --------------

            TABLE #3 - store_items 
            ---------------------------------
            itemID | itemName 
            ---------------------------------

            RESULT
            -----------------------
            itemID | itemName | COUNT(store_sales) | COUNT(store_views)


            EXAMPLE:
            -----------------------
            itemID | itemName | COUNT(store_sales) | COUNT(store_views) 
            1         PHONE             3                   45 
            2         BOOK              5                   61

MY CODE WITH ERROR:

            SELECT
            a.itemID    ,
            b.itemID    ,
            c.itemID    ,
            COUNT(if(a.itemID   = c.itemID  , a.itemID  , NULL)) AS views       ,
            COUNT(if(b.itemID   = c.itemID  , b.itemID  , NULL)) AS sales               

            FROM store_views a

            LEFT JOIN store_sales b
            ON a.itemID = b.itemID 

            LEFT JOIN store_items c
            ON a.itemID = c.itemID

            WHERE 1=1
            GROUP BY itemID

Don't you have any primary key for store_sales and store_views ? — M Khalid Junaid, Oct 10 '17 at 19:01
no, each time someone visit a store item it creates a new record using the item id and the timedate stamp. The same goes for store_sales — collado.17, Oct 10 '17 at 19:07

score 0 · Accepted Answer · answered Oct 10 '17 at 19:09

You can get count for sales and views separately in sub selects and then do a left join with your item table, Used COALESCE to skip null records for count

SELECT
a.itemID ,
COALESCE(b.item_sales,0),
COALESCE(c.item_views,0)
FROM  store_items a
LEFT JOIN (
    SELECT itemID,COUNT(*) as item_sales
    FROM store_sales
    GROUP BY itemID  
) b ON a.itemID = b.itemID 
LEFT JOIN (
    SELECT itemID,COUNT(*) as item_views
    FROM store_views v
    GROUP BY itemID
) c ON a.itemID = c.itemID

mySQL JOIN three tables - Ecommerce items , views and sales

MY CODE WITH ERROR:

1 Answers1