CODEWITHSUNDEEP
mysqlsql
jamiemax
jamiemax

Reputation: 189

SQL - get count from one table based on result of query

I have a two tables, one which contains details of events (name,time,etc), and one which contains records of bookings customers have made for each of these events. I want to be able to query the database and get a set of results that contains all the fields in the events table, plus the number of bookings that there have been for each of these events, so something like:

event_name, event_time, total_spaces_available, number_of_bookings

At the moment I'm getting around this by looping through the results in PHP but is it possible to do within SQL?

I want to be able to do something like:

SELECT *, COUNT(SELECT * FROM bookings WHERE event_id=<VARIABLE>) FROM events

But i don't know the syntax

I'm in MySQL

Any help would be much appreciated!

Upvotes: 0

Views: 138

Answers (2)

TiiJ7
TiiJ7

Reputation: 3392

Possible solution with a correlated subquery:

SELECT e.event_name,
  e.event_time,
  e.total_spaces_available,
  (SELECT count(*) FROM bookings b WHERE b.event_id=e.event_id) AS number_of_bookings
FROM events e

Edit: Like mentioned in the comments, this may be less performant than the join solution. Although I believe the latest MySQL converts them to joins anyway.

Upvotes: 3

saamorim
saamorim

Reputation: 3905

Do a group by

Select event_name, event_time, total_spaces_available, count(1) number_of_bookings
  from events 
    inner join booking on events.id = bookings.event_id
  group by event_name, event_time, total_spaces_available

Upvotes: 1

Related Questions