CODEWITHSUNDEEP
mysqljoin
BMN
BMN

Reputation: 8508

Get all users even if no records in another table

I'm facing a problem here :

I have two tables :

A users table :

+----+---------------+----------+
| id | username      | company  |
+----±---------------±----------+
| 1  | John          | 0        |
| 2  | Jack          | 0        |
| 3  | Casimir       | 0        |
±----±---------------±----------±

A orders table :

+----+---------------+----------+--------+
| id | date          | iduser   | status |
+----±---------------±----------+--------+
| 1  | 2012-05-28    | 1        | 1      |
| 2  | 2012-05-25    | 1        | 1      |
| 3  | 2012-04-28    | 2        | 1      |
| 4  | 2012-03-28    | 1        | 1      |
| 5  | 2012-02-28    | 2        | 0      |
±----±---------------±----------±--------+

What I'm trying to do is to get a result like this :

+----------+---------------+-------------+
| username | COUNT(order)  | MAX(date)   |
+----------±---------------±-------------+
| John     | 3             | 2012-05-28  |
| Jack     | 1             | 2012-04-28  |
| Casimir  | 0             | NULL        |
±----------±---------------±-------------±

Here's the request I have for the moment :

SELECT u.username, COUNT(o.id), MAX(o.date)
FROM users u
INNER JOIN orders ON u.id = o.iduser
WHERE o.status = 1
GROUP BY u.id

This request gives me a result like :

+----------+---------------+-------------+
| username | COUNT(order)  | MAX(date)   |
+----------±---------------±-------------+
| John     | 3             | 2012-05-28  |
| Jack     | 1             | 2012-04-28  |
±----------±---------------±-------------±

As you can see, the user Casimir is not shown as he made no order. How can I modify my request to get the result I need please ?

Thanks !

Upvotes: 2

Views: 1205

Answers (2)

Widor
Widor

Reputation: 13275

You need to use an OUTER JOIN instead of your current INNER JOIN.

Have a look at Jeff's post here to see how they differ: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Upvotes: 2

trapper
trapper

Reputation: 11993

A LEFT JOIN or LEFT OUTER JOIN will include all rows of the inital table, including those where there is no match in the joined-to table

SELECT u.username, COUNT(o.id), MAX(o.date)
FROM users u
LEFT OUTER JOIN orders o ON u.id = o.iduser AND o.status = 1
GROUP BY u.id

Upvotes: 4

Related Questions