That will be my very first query using subquery ever but I already have problems.
Let's take a look at my tables:
user - id - name user_visit - id - user_id - visit_type_id (BOOKED, CANCELED) - visit_starts_at - visit_ends_at type_of_visit - id - type
And the result of it should be as follows:
['name' => 'John Doe', 'bookings' => 138, 'cancellations' => 30], ['name' => 'Jane Smith', 'bookings' => 47, 'cancellations' => 12], ['name' => 'Fleur V.', 'bookings' => 12, 'cancellations' => 1]
So it should count how many bookings and cancellations had user.
So far I have started with the following query:
SELECT u.name AS `name`, COUNT(booking.id) AS `bookings` FROM `user` AS `u`, (SELECT * FROM `user_visit` AS `uv` INNER JOIN type_of_visit AS `tov` ON uv.visit_type_id = tov.id WHERE tov.type = 'BOOKED' ) AS `booking` GROUP BY u.id
Unfortunately it does not work properly because it was returning only one user, so something went wrong. Anyway you guys have more experience and you probably know how would you handle this, so anyone?