Mysql query help

Question

My system consists of two user types - Students and Tutors.
- Tutors can create classes and packs
- Both Students and tutors can purchase classes and packs

Following are the tables involved

Groups
Users- Contains common fields of both user types
Tutor_Details- Tutor specific fields
WebClasses - Classes created by tutors
Learning_Packs - Packs created by tutors Orders - One record per purchase
Order_Details - Multiple records per purchase - as many items in purchase
Payments

The following query produces the list of all users(students and tutors) and displays the 3 fields- User name, orders - number of items purchased and topics - total number of classes and packs created -

SELECT u.name,
       COUNT(DISTINCT( o.id_order ))                                         AS
       orders,
       ( ( COUNT(DISTINCT( wc.id_wc )) ) + ( COUNT(DISTINCT( lp.id_lp )) ) ) AS
       topics
FROM   users AS u
       LEFT JOIN tutor_details AS td
         ON u.id_user = td.id_user
       INNER JOIN groups AS g
         ON u.id_group = g.id_group
       LEFT JOIN webclasses AS wc
         ON td.id_tutor = wc.id_author
       LEFT JOIN learning_packs AS lp
         ON td.id_tutor = lp.id_author
       LEFT JOIN orders AS o
         ON ( u.id_user = o.id_user )
       LEFT JOIN order_details AS od
         ON ( o.id_order = od.id_order )
       LEFT JOIN payments AS p
         ON ( o.id_order = p.id_order )
WHERE  IF(o.id_order != 0, o.order_status = 'paid', 1)
       AND IF(p.id_payment != 0, p.payment_status = 'success', 1)
GROUP  BY u.id_user
ORDER  BY u.id_user ASC 

Help needed
Now, I want to add another filter/condition to the topics counting. Only those topics should be counted for which wc.status=1 or lp.status=1. I wish to do the same in a single query. Please note that the condition cannot be added inside the main where block (before the group block) because the query must still display students (who have not taken any class) and tutors who have taken classes with id_status = 0.

The topics count should only consider the classes/packs condition, that's all I want. The query should still display all the users as displayed by the present query.

Thanks,
Sandeepan

Answer 1

Okay, I found the solution myself before anyone else :)

SELECT u.id_user,
       ( Concat(u.name, ' ', u.surname) )                                AS name
       ,
       u.login,
       u.status,
       u.email,
       g.name                                                            AS TYPE
       ,
       u.joined,
       COUNT(DISTINCT( o.id_order ))                                     AS
       orders,
       ( COUNT(DISTINCT( IF(wc.id_status = 1, wc.id_wc, NULL) )) +
           COUNT(DISTINCT( IF(lp.id_status = 1, lp.id_lp, NULL) )) ) AS
       topics
FROM   users AS u
       LEFT JOIN tutor_details AS td
         ON u.id_user = td.id_user
       INNER JOIN groups AS g
         ON u.id_group = g.id_group
       LEFT JOIN webclasses AS wc
         ON td.id_tutor = wc.id_author
       LEFT JOIN learning_packs AS lp
         ON td.id_tutor = lp.id_author
       LEFT JOIN orders AS o
         ON ( u.id_user = o.id_user )
       LEFT JOIN order_details AS od
         ON ( o.id_order = od.id_order )
       LEFT JOIN payments AS p
         ON ( o.id_order = p.id_order )
WHERE  IF(o.id_order != 0, o.order_status = 'paid', 1)
       AND IF(p.id_payment != 0, p.payment_status = 'success', 1)
GROUP  BY u.id_user
ORDER  BY u.id_user ASC