1

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!

1

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 !!!

1
  • Boom! You nailed it Rolando. Thanks so much. I did change the table name from event_id to event above to make two of the queries work. You rock! Dec 3 '14 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.