-2

I am trying to get all records from my tables (groups, fiches, clients, goals, factures) by using left join to get results, and get 0 if the user id is not present (ex : no goal assigned to the membre) I dont know if i explain effectively but here is my query :

select CONCAT(s.firstname, ' ', s.lastname) AS Membre, g.achievement AS Objectif, count(l.assigned) AS `num_l`, count(c.id_fiche) AS `num_c`,
(count(c.id_fiche)/count(l.assigned))*100 AS `perc`, SUM(f.total) AS `glob`, (g.achievement - SUM(f.total)) AS `rest`, (SUM(f.total)/g.achievement)*100 AS `taux` 
from `groups` s 
LEFT JOIN `fiches` l ON s.id_membre=l.assigned LEFT JOIN `clients` c ON l.id=c.id_fiche 
LEFT JOIN `goals` g ON s.id_membre=g.id_membre LEFT JOIN `factures` f ON s.id_membre=f.addedfrom 
WHERE l.dateassigned BETWEEN  '2020-08-01' AND '2020-08-31' AND f.status=2 
GROUP BY s.id_membre

All I get in the result is members who for whom the goal is assigned , but what i need is all members, and for whom the goal is not assigned get 0 in fields related to goal. Thank you.

salah eddine Koraich
  • 1
  • 2
  • Here is my tables : `groups(id_membre, firstname, email, password)` `fiches(id, name, company, assigned, dateassigned)` `clients(userid, phone, datecreated, id_fiche)` `goals(id, goal_type, achievement, start_date, end_date, id_membre)` `factures(id, clientid, total, status, addedfrom)` – salah eddine Koraich Aug 06 '20 at 13:36

3 Answers3

0

The LEFT JOIN is fine. You have opted to turn it into an INNER JOIN by adding a WHERE clause.

When using LEFT JOIN, filtering conditions on all but the first table should be in the ON conditions.

select CONCAT(s.firstname, ' ', s.lastname) AS Membre, g.achievement AS Objectif, 
       count(l.assigned) AS `num_l`, count(c.id_fiche) AS `num_c`, 
       (count(c.id_fiche)/count(l.assigned))*100 AS `perc`, SUM(f.total) AS `glob`, (g.achievement - SUM(f.total)) AS `rest`,
      (SUM(f.total)/g.achievement)*100 AS `taux`
from `groups` s LEFT JOIN
     `fiches` l
     ON s.id_membre = l.assigned AND
        l.dateassigned BETWEEN '2020-08-01' AND '2020-08-31' LEFT JOIN
     `clients` c
     ON l.id = c.id_fiche LEFT JOIN
     `goals` g
     ON s.id_membre = g.id_membre LEFT JOIN
     `factures` f
     ON s.id_membre = f.addedfrom AND f.status=2
GROUP BY s.id_membre
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You need to chaneg you r query to -

select CONCAT(s.firstname, ' ', s.lastname) AS Membre,
       g.achievement AS Objectif,
       count(l.assigned) AS `num_l`,
       count(c.id_fiche) AS `num_c`,
       (count(c.id_fiche)/count(l.assigned))*100 AS `perc`,
       SUM(f.total) AS `glob`,
       (g.achievement - SUM(f.total)) AS `rest`,
       (SUM(f.total)/g.achievement)*100 AS `taux`
  from `groups` s
  LEFT JOIN `fiches` l ON s.id_membre=l.assigned
                       AND l.dateassigned BETWEEN  '2020-08-01' AND '2020-08-31'
  LEFT JOIN `clients` c ON l.id=c.id_fiche
  LEFT JOIN `goals` g ON s.id_membre=g.id_membre
  LEFT JOIN `factures` f ON s.id_membre=f.addedfrom
                         AND f.status=2
 GROUP BY s.id_membre
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
  • thank you but when i try it i didnt get the correct number of the staff, i have 8 members i get 5 records, and in the field of Membre i get `'NULL'` – salah eddine Koraich Aug 06 '20 at 13:08
0

You're only running left joins, so without knowing all the tables my first instinct is that the members themselves are missing from the 'groups' table, the first table selected. Alternatively, try removing the WHERE and AND criteria and seeing if the missing users come back.

Aware
  • 1
  • 1