sweebee sweebee - 1 month ago 5
MySQL Question

Count rows in table with certain value in other table

I need a quick way to find the Number of items in a table. The items are linked to an other table. Table 1 is products and table 2 is orders.

Orders contains a paid status (1 or 0).

Orders table example:

id paid
1 0
2 1


Products table example:

id orderid type
1 1 5
2 1 5
3 1 3
4 2 5
5 2 5
6 2 3


Products contains a id (orderid) that refers to the order and a type. So i need the number of products where type = 5 and paid = 1 in the orders table.

What is the best and fastest way to archieve this?

So I need all the paid products with type 5. The result should be '2'.

Answer

you can use join like this,

SELECT COUNT(*) AS num_rows 
FROM products 
LEFT JOIN orders ON orders.id = products.orderid 
WHERE type = 5 AND paid = 1
Comments