MySQL join for two queries

join;MySQL

I have the following two queries…

mysql> select id, title from events;
+----+-----------------------------------------------------------------------------------------------+
| id | title                                                                                         |
+----+-----------------------------------------------------------------------------------------------+
|  2 | Second planning meeting for Musta                                           |
|  3 | Writing for Online Audiences                                                                  |
|  4 | Beginning with the Wintas Header - Aug 2010 |
|  5 | Sausage Monthly Meetup                                                              |
|  6 | Third planning meeting for Musta                                            |
+----+-----------------------------------------------------------------------------------------------+

mysql> select event_id, count(*) as num_bookmarks from event_connections where connectiontype = 3 group by event_id;
+----------+---------------+
| event_id | num_bookmarks |
+----------+---------------+
|        2 |             1 |
|        3 |             2 |
|        4 |             4 |
|        5 |             8 |
|        6 |             1 |
+----------+---------------+
5 rows in set (0.01 sec)

The 2nd query shows me a total of bookmarks (connectiontype 3) for each event_id. I would like a single query that will show me an event in addition to the total number of bookmarks.

My best effort was…

mysql> select event_id, count(*) as num_bookmarks from event_connections where connectiontype = 3 join event_id on events.id=event_connections.event_id group by event_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'join event_id on events.id=event_connections.event_id group by event_id' at line 1

The end result would look like be the output from the first query; select id, title from events in addition to a a num_bookmarks column that has a count of bookmarks for that event.

Thanks in advance!

Best Answer

YOUR ORIGINAL QUERY

select event_id, count(*) as num_bookmarks from event_connections
where connectiontype = 3 join event_id on events.id=event_connections.event_id group by event_id;

PROPER SYNTAX

select event_id, count(*) as num_bookmarks
from event_connections join events on events.id=event_connections.event_id
where event_connections.connectiontype = 3
group by event_id;

You just had the WHERE in the wrong place

SUGGESTIONS

If all you care about is the event_id and the counts, do this

select event_id, count(*) as num_bookmarks
from event_connections
where connectiontype = 3
group by event_id;

No need to join.

If you want the event title, then you must join

select events.title, count(*) as num_bookmarks
from event_connections join events on events.id=event_connections.event_id
where event_connections.connectiontype = 3
group by events.title;

GIVE IT A TRY !!!

Related Question