CODEWITHSUNDEEP
mysqlsqlgroup-by
user2570937
user2570937

Reputation: 852

SQL Selecting count from join returning 0 instead of null result

I have a query that gets data and also joins another table (A) and counts the rows in that join table (B). However if the main table (A) is empty I want the query to return nothing. However it is returning a result of null for id and date and an integer value of 0 for users instead of a null row. How do I get an empty result instead of it returning something?

Returning:

id   | date | users
null | null | 0

SQL Code

SELECT
    `sessions`.`id`,
    `sessions`.`date`,
    COUNT( sessions_users.id ) AS users 
FROM
    `sessions`
    LEFT JOIN `sessions_users` ON `sessions`.`id` = `sessions_users`.`sessions_id`

Upvotes: 0

Views: 1656

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

From your description, it sounds like you want an inner join:

SELECT s.id, s.date, COUNT(*) as users
FROM sessions s JOIN
     sessions_users su
     ON su.id = su.sessions_id;

Upvotes: 0

FanoFN
FanoFN

Reputation: 7114

If I understand correctly, instead of NULL, you want something like this:

id   | date | users
     |      | 0

If so, just simply use IFNULL() in your SELECT as such:

SELECT
    IFNULL(`sessions`.`id`,' ') as id,
    IFNULL(`sessions`.`date`,' ') as date,
....

There are also a few other ways to achieve this using just IF() or CASE .. but IFNULL is very straight forward.

BUT if you don't want to see any NULL and 0 values, change your LEFT JOIN to INNER JOIN and you're done.

Upvotes: 0

GMB
GMB

Reputation: 222492

An aggregate query without a group by clause always returns a single record, regardless of the content of the underlying result set (and even if it is empty).

But, since you have non-aggregated columns in the select clause (sessions.id and sessions.date), your query is missing a group by clause anyway. In non-ancient versions in MySQL, where sql mode ONLY_FULL_GROUP_BY is enabled by default, this is a syntax error.

Consider:

SELECT
    `sessions`.`id`,
    `sessions`.`date`,
    COUNT( sessions_users.id ) AS users 
FROM
    `sessions`
    LEFT JOIN `sessions_users` ON `sessions`.`id` = `sessions_users`.`sessions_id`
GROUP BY
    `sessions`.`id`,
    `sessions`.`date`

This will produce one record per session id and date, along with the count of matching records in sessions_users. If there are no records in sessions, the query will return an empty result set.

Upvotes: 1

Related Questions