Mike Mike - 1 month ago 4
MySQL Question

Mysql query subselect getting NULL

I'm trying to join over three tables and get the active plan of a vendor. It is possible, that the vendor had a lot of plans in the past, but the active on is that counts.
The whole query is bigger (counting items he has aso) and because of that i did it with a subselect, but for this example it should be enough.

I always get plantitle and planstatus of NULL. How can i fix this?

Query

SELECT v.title
, plans.title AS plantitle
, uplans.status AS planstatus
, uplans.uid
, COUNT(DISTINCT obs.id) AS obj_count
, sum(case when obs.published = -1 then 1 else 0 end) trash
, sum(case when obs.published = 1 then 1 else 0 end) published
, sum(case when obs.published = 0 then 1 else 0 end) unpublished
FROM `vendors` AS v
LEFT JOIN objects AS obs ON obs.vid = v.id
LEFT JOIN `userplans` AS uplans ON uplans.uid = (
SELECT up.id
FROM `userplans`AS up
WHERE up.uid=v.uid AND status = "ACTIVE" LIMIT 1)
LEFT JOIN `plans` AS plans ON plans.id=uplans.pid
GROUP BY v.id
ORDER BY v.id asc


Tables

Vendors
id, uid, title
10, 1, Name 1
20, 4, Name 2
30, 5, Name 3

Plans
id, title
40, Plan 1
50, Plan 2

Userplans
id, uid, pid, status
1, 1, 40, CANCELED
2, 1, 50, CANCELED
3, 1, 40, CANCELED
4, 4, 50, CANCELED
5, 4, 50, CANCELED
6, 4, 50, ACTIVE
7, 1, 40, ACTIVE

Answer

Lets get the object counts 1st as the associations to other tables may be 1-M which would result in larger counts. then join to the other needed information.

This still assumes that a the combination of a user and plan in userPlan can only have 1 active record. If it can have more than 1 I still need to know which active userPlan to select.

Also why the left joins? are you after all vendors regardless of plans and objects and userplans? Is it possible that a vendor HAS no active plans in which case the title would be null?

SELECT v.title
     , P.title AS plantitle
     , UP.status AS planstatus
     , up.uid
     , O.obj_count
     , O.trash
     , O.published
     , O.unpublished
FROM vendors v    
LEFT JOIN userplans  UP
  ON V.uid = UP.UID
 AND UP.status = 'ACTIVE'
LEFT JOIN (SELECT obs.VID
            ,COUNT(DISTINCT obs.id) AS obj_count
            ,sum(case when obs.published = -1 then 1 else 0 end) trash
            ,sum(case when obs.published = 1 then 1 else 0 end) published
            ,sum(case when obs.published = 0 then 1 else 0 end) unpublished
            FROM OBJECTS obs
            GROUP BY obs.VID) O  
  ON O.vid = v.id
LEFT JOIN `plans` P 
  ON P.id=UP.pid

ORDER BY v.id asc
Comments