CODEWITHSUNDEEP
mysqlsql
Kevin Lee
Kevin Lee

Reputation: 1089

My Where condition isn't returning every row in a table, how to make it return the rows?

I have two tables that needs to be linked together, I want to count the number of instances of the category in the name table

SELECT category.name, COUNT(name.category) as availability FROM
        category LEFT JOIN name ON name.category = category.name
        GROUP BY name.category

This is great but I want the query to only count the names that is not yet over now. So I add a WHERE condition time > now()

SELECT category.name, COUNT(name.category) as availability FROM
        category LEFT JOIN name ON name.category = category.name WHERE
        time > now()
        GROUP BY name.category

So far so good, this query returns only the rows that have the time that is farther from the future. But it isn't returning every category from the category table. This query returns only those categories that have instances from the name table that have set time farther from the future.

What I want is to return all the categories from the category table.. If the category don't have an instance in the name table, I still want it to return but the availability will be 0

Upvotes: 1

Views: 78

Answers (5)

vbence
vbence

Reputation: 20333

First: you should have said which table holds the time column, or at least signal it in your SQL. I assume it is in the name table. In this case just substitute WHILE with AND like this:

SELECT category.name,
    COUNT(name.category) as availability
FROM category 
    LEFT JOIN name ON name.category = category.name AND name.time > now()
GROUP BY category.name

Upvotes: 2

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

I'm not sure about mysql but the following works in Oracle:

SELECT category.name, COUNT(name.category) as availability FROM
        category LEFT JOIN name ON name.category = category.name WHERE
        (name.category is null or name.time > now() )
        GROUP BY category.category

Upvotes: 1

RollingBoy
RollingBoy

Reputation: 2817

What about this:

SELECT category.name, case when time > now() then COUNT(name.category) else 0 end 
    as  availability FROM
    category LEFT JOIN name ON name.category = category.name WHERE
    GROUP BY name.category

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239654

If time is a column from the name table, then all conditions that should be applied to the left joined table must be done in the ON clause. Otherwise, the WHERE clause will transform it back into an INNER JOIN:

SELECT category.name, COUNT(name.category) as availability FROM
    category LEFT JOIN name ON name.category = category.name AND
    time > now()
    GROUP BY category.name

Basically, the WHERE clause applies to the entire (potential) result set that has been generated from your FROM clause (and all of your JOINs). The conditions in the where clause must be met by a row in this result set in order for further processing to occur. But in the case of a LEFT JOIN, if the join hasn't succeeded, the columns from that table will all be NULL. So unless your WHERE clause allows those columns to be NULL, they will not proceed further in the query.

Upvotes: 2

Richard Schneider
Richard Schneider

Reputation: 35477

Use an OUTER JOIN instead of an INNER JOIN to get all categories

Upvotes: 1

Related Questions