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?
, plans.title AS plantitle
, uplans.status AS planstatus
, 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 = (
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
id, uid, title
10, 1, Name 1
20, 4, Name 2
30, 5, Name 3
40, Plan 1
50, Plan 2
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
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