MySQL COUNT Sub-query with SELECT *

Question

I have a table of inventory items (holds description, details of item etc.), a table of stock (physical items that we have - items of inventory), and a suppliers table (who supply the stock, but may differ from time to time).

Suppliers -- Stock -- Inventory

Inventory has many stock. Suppliers have many stock. Stock has one supplier, and one inventory

I'm trying to run a query to get all data from inventory, and count how many suppliers it has through a sub query. However, I need to use SELECT *

What I have at the moment:

SELECT 
     ( SELECT COUNT(DISTINCT SupplierID) 
         FROM Stock 
        WHERE Stock.InventoryID = Inventory.ID
     ) AS Suppliers
     , * 
  FROM `Inventory`;

I've tried variations on this, swapping the field order (seen this elsewhere on this site), changing the sub-query etc.

However, it tells me there's an error near '* FROM'. Can anyone suggest a way to do this query please?

Gordon Linoff · Accepted Answer · 2015-11-06 16:01:07Z

1

Use table aliases:

SELECT (SELECT COUNT(DISTINCT s.SupplierID) 
        FROM Stock s
        WHERE s.InventoryID = i.ID
       ) AS Suppliers, i.* 
FROM `Inventory` i;

The need for a qualification on * is described in the documentation:

Use of an unqualified * with other items in the select list may produce a parse error. To avoid this problem, use a qualified tbl_name.* reference
SELECT AVG(score), t1.* FROM t1 ...

answered Nov 6, 2015 at 16:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL COUNT Sub-query with SELECT *

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related