I need to get a list of the stores that the company has, and I need to output the number of products in each of those stores. The list of stores and items are different tables and I need to merge all of this to output a common answer. To have all the columns from the stores table and the itemscount column from the items table. So far I have this query, but it doesn't work.
SELECT * (SELECT COUNT(*) FROM items
WHERE store_name = (SELECT storename FROM stores
WHERE companyid = (SELECT id FROM companies
WHERE clientmail = ''email'))) AS itemcount FROM stores
WHERE companyid = (SELECT id FROM companies
WHERE clientmail = 'email');
I also tried this request, but it was wrong.
SELECT * FROM stores
WHERE companyid = (SELECT id FROM companies WHERE clientmail = 'email')
UNION
SELECT COUNT(*) AS itemscount FROM items
WHERE store_name = (SELECT storename FROM stores
WHERE companyid = (SELECT id FROM companies WHERE clientmail = 'email'));
I would like to get a complex sample from a query that accesses different tables and compounds the answers in one sample
