I have the following 2 queries and want to join them on store_id to get the # of stores that sell a dell product in a particular post code zone, whether the store is open, the total number of reviews this store had and the average rating of the reviews
SELECT s.* , sh . * , d . * , pd . *,
CASE
When (holiday = "Y") THEN "Holiday"
END AS open_status
FROM delivery d, products_description pd, store s, store_hours sh
WHERE d.deliver_to_postcode =5140
AND d.store_id = pd.store_id
AND (pd.products_name like '%dell%' or pd.products_description like '%dell%')
AND pd.store_id = s.store_id
AND s.store_id = sh.store_id
AND sh.weekday = dayname( curdate( ) )
GROUP BY d.store_id
SELECT store_id, count( * ) AS reviews, avg( reviews_rating ) AS rating
FROM reviews
WHERE reviews_status =1
Total stores | store_id | store_name | Postcode | reviews | rating | open_status
----------------------------------------------------------------------------------------------------------------------
2 | 1 | store 1 | 5140 | 3 | 2 star | Holiday
2 | 2 | store 2 | 5140 | 1 | 2 star | Holiday
How can I join these 2 queries to give the result above?