Performing an INNER JOIN across 2 tables with COUNT()

Question

I'm stuck on an SQL query and I'm hoping you guys can help me out.

I have 2 tables:

EVENTS
event_id (PK)
event_name

ORDERS
order_id (PK)
event_id (FK)

and I'm trying to perform the following query on those tables:

SELECT
    e.event_id,
    e.name,
    COUNT(o.event_id) AS booked     
FROM
    events AS e
INNER JOIN
    orders AS o
ON
    e.event_id = o.event_id
WHERE
    e.event_id IN (1, 2, 3)

The problem is that the result I'm getting is:

+----------+------+--------+
| event_id | name | booked |
+----------+------+--------+
|     NULL | NULL |      0 |
+----------+------+--------+

But when I run 3 separate queries with:

WHERE e.event_id IN (1)

WHERE e.event_id IN (2)

WHERE e.event_id IN (3)

I get the results I want:

+----------+-------+--------+
| event_id | name  | booked |
+----------+-------+--------+
|       1  | Test1 |      0 |
+----------+-------+--------+

+----------+-------+--------+
| event_id | name  | booked |
+----------+-------+--------+
|       2  | Test2 |      0 |
+----------+-------+--------+

+----------+-------+--------+
| event_id | name  | booked |
+----------+-------+--------+
|       3  | Test3 |      0 |
+----------+-------+--------+

What am I doing wrong? Is there a way to use only one query and get:

+----------+-------+--------+
| event_id | name  | booked |
+----------+-------+--------+
|       1  | Test1 |      0 |
+----------+-------+--------+
|       2  | Test2 |      0 |
+----------+-------+--------+
|       3  | Test3 |      0 |
+----------+-------+--------+

Please help.

UPDATE: When I run:

SELECT
    e.event_id,
    e.name,
    COUNT(o.event_id) AS booked     
FROM
    events AS e
LEFT JOIN
    orders AS o
ON
    e.event_id = o.event_id
WHERE
    e.event_id IN (1, 2, 3)

I get only:

+----------+-------+--------+
| event_id | name  | booked |
+----------+-------+--------+
|       1  | Test1 |      0 |
+----------+-------+--------+

Answer 1

Using left join with a if condition could do it

SELECT
  e.event_id,
  e.name,
  SUM(IF(o.event_id IS NULL, 0, 1)) AS booked
FROM events AS e
  LEFT JOIN orders AS o
    ON e.event_id = o.event_id
WHERE e.event_id IN(1, 2, 3)
GROUP   BY e.event_id, e.name

Fiddle Demo

Or you could do it like this too

SELECT
  e.event_id,
  e.name,
  COUNT(o.event_id) AS booked
FROM events AS e
  LEFT JOIN orders AS o
    ON e.event_id = o.event_id
WHERE e.event_id IN(1, 2, 3)
GROUP BY e.event_id, e.name

SQL Fiddle Demo

Answer 2

You should be using LEFT JOIN instead of INNER JOIN and don't forget to use GROUP BY clause since you are using aggregate function COUNT().

SELECT  e.event_id,
        e.name,
        COUNT(o.event_id) AS booked     
FROM    events AS e
        LEFT JOIN orders AS o
            ON e.event_id = o.event_id
WHERE   e.event_id IN (1, 2, 3)
GROUP   BY e.event_id, e.name

• SQLFiddle Demo

To further gain more knowledge about joins, kindly visit the link below:

• Visual Representation of SQL Joins