MYSQL ordering data from 1 table using the 2nd table

Question

Is there a MYSQL query I can use to order items from a second table.

My example I'm listing My Stores... each store has a number of products in a diffrent table. so my store listing displays the number of products in that store..

is there an easy MYSQL query so i can order my stores by number of products

my store doesnt hold a value of the number, it counts the rows in the 2nd table

Answer 1

It should be simple enough as long as you have something to join to the second table on.

Select a.* From `FirstTable` a
Inner Join `SecondTable` b on a.SomeCol = b.SomeCol
Order By b.OrderingCol

Answer 2

If I understood your problem correctly, you may want to try something like the following:

SELECT     stores.id store_id, COUNT(products.id) num_products
FROM       stores
LEFT JOIN  products ON (stores.id = products.store_id)
GROUP BY   stores.id
ORDER BY   num_products;

Test case:

CREATE TABLE stores (id int, name varchar(10));
CREATE TABLE products (id int, store_id int);

INSERT INTO stores VALUES (1, 'store 1');
INSERT INTO stores VALUES (2, 'store 2');
INSERT INTO stores VALUES (3, 'store 3');
INSERT INTO stores VALUES (4, 'store 4');

INSERT INTO products VALUES (1, 1);
INSERT INTO products VALUES (2, 1);
INSERT INTO products VALUES (3, 1);

INSERT INTO products VALUES (4, 2);

INSERT INTO products VALUES (5, 3);
INSERT INTO products VALUES (6, 3);

Result:

+----------+--------------+
| store_id | num_products |
+----------+--------------+
|        4 |            0 |
|        2 |            1 |
|        3 |            2 |
|        1 |            3 |
+----------+--------------+
4 rows in set (0.00 sec)

You can also sort in descending order by adding DESC after ORDER BY num_products:

+----------+--------------+
| store_id | num_products |
+----------+--------------+
|        1 |            3 |
|        3 |            2 |
|        2 |            1 |
|        4 |            0 |
+----------+--------------+
4 rows in set (0.00 sec)