2

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?

2

I hope that query is self-explaining enough.

SELECT u.name AS `name`
     , IFNULL( z.bookings, 0 ) AS bookings
     , IFNULL( q.cancellations, 0 ) AS cancellations 
  FROM user AS u 

  LEFT JOIN 
     (
       SELECT uv.user_id AS user_id
            , COUNT(*) AS `bookings`
         FROM user_visit AS uv
        WHERE uv.type = 'BOOKED'
        GROUP BY uv.user_id 
     )
     AS z 
     ON z.user_id = u.id

  LEFT JOIN 
     (
       SELECT uv.user_id AS user_id
            , COUNT(*) AS `cancellations`
         FROM user_visit AS uv
        WHERE uv.type = 'CANCELED'
        GROUP BY uv.user_id
     ) 
     AS q 
     ON q.user_id = u.id
;
  • amazing! thank you, I have adjusted it to my needs and works like a charm. – undefinedman Jun 22 '17 at 20:51
  • You have to add the index (user_id, visit_type_id) for better performance – Kondybas Jun 22 '17 at 20:54
0

There's a problem with your INNER JOIN. Your condition says

ON uv.visit_type_id ON tov.id

It should be saying something like

ON uv.visit_type_id = tov.id

Plus, you are not joining your subquery with the user that actually made the visit type. You have to join the user_id from your user_visit to the user.id

If you're to write subqueries, I would do a "WITH". It's a personal preference but I find it more readable.

It should look something like this.

;With BookedVisits AS
(
SELECT uv.User_id
FROM user_visit uv
JOIN  type_of_visit tov on uv.visit_type_id = tov.id
WHERE tov.type = 'BOOKED'
)
SELECT u.Name as Name,
  COUNT(booking.id) AS bookings
FROM user u
JOIN BookedVisits v on v.User_id=u.id
GROUP BY u.Name

I think you can even do this in one query, without subqueries.

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.