CODEWITHSUNDEEP
mysqlsql
Stilero
Stilero

Reputation: 461

JOIN query not behaving as expected

I'm working on a query that doesn't behave as expected, and would appreciate any help on pointing me in the right direction.

TABLES I have three central tables consists of the following.

table_categories
- id (int)
- name (varchar)

table_events
- id (int)
- name (varchar)
- created (date time)

table_taxonomy
- cat_id (id from table_categories)
- event_id (id from table_events)
- type (varchar in this example always 'category')

GOAL Count events created after a certain date in each category. The result should be something like:

COUNT   NAME    ID
3       Rock     1
2       Punk     3

QUERY This is the query that I've come up with. The problem is that the result doesn't care about the created date, and grabs all events regardles of when they where created.

SELECT COUNT(*) AS count,
            table_categories.name,
            table_categories.id
            FROM table_taxonomy
            INNER JOIN table_categories
            ON table_categories.id = table_taxonomy.cat_id
            LEFT JOIN table_events
            ON table_events.id = table_taxonomy.events_id
            WHERE table_taxonomy.type = 'category'
            AND table_taxonomy.cat_id IN(2,3)
            AND table_events.created > '2012-10-07 05:30:00'
            GROUP BY (table_categories.name)

Upvotes: 2

Views: 76

Answers (2)

Naresh J
Naresh J

Reputation: 2127

try this one, just small change while comparing date :

SELECT COUNT(*) AS count,
            table_categories.name,
            table_categories.id
            FROM table_taxonomy
            INNER JOIN table_categories
            ON table_categories.id = table_taxonomy.cat_id
            LEFT JOIN table_events
            ON table_events.id = table_taxonomy.events_id
            WHERE table_taxonomy.type = 'category'
            AND table_taxonomy.cat_id IN(2,3)
            AND Date(table_events.created) > '2012-10-07 05:30:00'
            GROUP BY (table_categories.name)

Upvotes: 3

John Woo
John Woo

Reputation: 263693

Try this one,

SELECT  c.ID, c.Name, COUNT(*)
FROM    table_categories a
        INNER JOIN table_taxonomy b
            ON a.id = b.cat_id
        INNER JOIN table_events c
            ON b.event_ID = c.ID
WHERE   c.created > 'dateHere' 
        -- AND .....                  -- other conditions here
GROUP BY    c.ID, c.Name

Upvotes: 1

Related Questions