INNER JOIN and SORTING issue in mysql

Question

This is kinda hard to explain, therefore I'm gonna do this step by step

I have created a table called list_names, there I store the list of names such as (George, Tina, Bobby, etc...)

I have also created another table which called names_items, this table is basically where I store how many items this person has, it looks something like this:

id | name | Item
1  | Tina | Bag
2  | Tina | Pencil
3  | Bob  | TV
4  | Bob  | Computer

My goal is, to display all the names from list_names then count the sum of the items of each person has from names_items and join them together. if the person haven't bought anything yet, return 0. I also want to sort the result by the sum of the items of this person bought. Is there anyway to accomplish this using mysql only?

Joachim Isaksson · Accepted Answer · 2013-06-30 06:16:29Z

2

If I understand you correctly, this should do it;

SELECT names.name, COUNT(item) AS items
FROM names
LEFT JOIN names_items 
  ON names.name = names_items.name
GROUP BY names.name
ORDER BY COUNT(item) DESC

An SQLfiddle to test with.

answered Jun 30, 2013 at 6:16

Joachim Isaksson

182k28 gold badges298 silver badges308 bronze badges

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

Comments

Ja͢ck · Accepted Answer · 2013-07-01 02:27:42Z

1

Sure, just use a left join between names and names_items.

SELECT names_items.name COUNT(item) AS itemcount
FROM names
LEFT JOIN names_items ON names.name = names_items.name
GROUP BY names_items.name
ORDER BY itemcount DESC

If a name has no items, the respective item will be NULL and COUNT(item) will return 0.

edited Jul 1, 2013 at 2:27

answered Jun 30, 2013 at 6:11

Ja͢ck

174k39 gold badges269 silver badges317 bronze badges

Comments

Moeed Farooqui · Accepted Answer · 2013-06-30 06:39:18Z

0

SELECT 
  list_names.name,
  names_items.item,
  COUNT(names_items.name) AS `Count Per Person` 
FROM
  list_names 
  INNER JOIN names_items 
    ON (list_names.id = names_items.id) 
GROUP BY names_items.name

edited Jun 30, 2013 at 6:39

answered Jun 30, 2013 at 6:17

Moeed Farooqui

3,6281 gold badge20 silver badges23 bronze badges

Collectives™ on Stack Overflow

INNER JOIN and SORTING issue in mysql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related