CODEWITHSUNDEEP
mysql
opstalj
opstalj

Reputation: 900

mysql: query combining info from 2 tables

Suppose the following configuration:

  1. Table 1 "generic" with fields: id & name
  2. Table 2 "info" with fields: userId (and some others)

When there is info to be stored, there will be records added to the info table. I can easily get a nice overview of the number of records for each user like:

mysql> SELECT userId ,COUNT(*) as nbr_entries FROM info GROUP BY userId ORDER BY nbr_entries DESC;
+-----------+-------------+
| userId    | nbr_entries |
+-----------+-------------+
| 3987      |        2254 |
| 11220     |        1922 |
...

Now, my question: I would like to have each userId to be looked up in Table 1 (generic), so that my query result would like like:

+-----------+-----------+-------------+
| name      | userId    | nbr_entries |
+-----------+-----------+-------------+
| Peter     | 3987      |        2254 |
| Walter    | 11220     |        1922 |
...

Any idea how to achieve this? Thanks in advance.

Upvotes: 1

Views: 29

Answers (3)

Paul Spiegel
Paul Spiegel

Reputation: 31792

Use a LEFT JOIN:

SELECT g.name, g.id as userId, COUNT(i.userId) as nbr_entries
FROM generic g
LEFT JOIN info i ON i.userId = g.id
GROUP BY g.id
ORDER BY nbr_entries DESC

This way you will also include users who has no info entries (yet) and get 0 as nbr_entries.

Upvotes: 1

Muhammad Saqlain
Muhammad Saqlain

Reputation: 2212

You can use subquery & JOIN

SELECT t1.name, t2.userId , t2.nbr_entries 
FROM generic t1
JOIN
(SELECT userId ,COUNT(*) as nbr_entries FROM info GROUP BY userId) t2
ON t1.userId = t2.userId ORDER BY t2.nbr_entries DESC;

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You could use JOIN

SELECT b.name,userid,COUNT(*) as nbr_entries FROM info a
inner join generic b on a.userid=b.id
GROUP BY b.name,userId 
ORDER BY nbr_entries DESC;

Upvotes: 1

Related Questions