-1

i have this 3 tables in a MySql Database.

users
+----+------+--------+------+
| Id | Name | Status | Role |
+----+------+--------+------+
|  1 | A    | Aktiv  | Op   |
|  2 | B    | Aktiv  | Op   |
|  3 | C    | Aktiv  | Op   |
|  4 | D    | Aktiv  | Op   |
+----+------+--------+------+

cnt
+----+------+------------+------+
| Id | Name | Date       | Type |
+----+------+------------+------+
|  1 | A    | 2017-11-09 | Web  |
|  2 | B    | 2017-11-09 | Web  |
|  3 | C    | 2017-11-09 | Web  |
|  4 | C    | 2017-11-09 | Inb  |
|  5 | A    | 2017-11-09 | Web  |
+----+------+------------+------+

Lead
+----+------+------------------+------------+
| Id | Name | Date             | Type       |
+----+------+------------------+------------+
|  1 | A    | 2017-11-09 00:24 | Imported   |
|  2 | B    | 2017-11-09 09:32 | Activation |
|  3 | B    | 2017-11-09 10:56 | Activation |
|  4 | D    | 2017-11-09 12:21 | Activation |
|  5 | D    | 2017-11-10 12:22 | Activation |
+----+------+------------------+------------+

I'm trying to join them in a main table but with no success, the query i'm using is:

SELECT IFNULL(u.Name,'Total') as "Name",
Sum(IF(c.Type = 'Web',1,0)) as "Cnt Web",
Sum(IF(l.Type = 'Activation',1,0)) as "Lead Web"
FROM users u
LEFT JOIN cnt c ON u.Name = c.Name and c.Date = '2017-11-09'
LEFT JOIN lead l ON u.Name = l.Name and l.Date>= '2017-11-09' AND l.Date< '2017-11-10'
WHERE u.Status = 'Aktiv' AND u.Role = 'Op'
GROUP BY u.Name WITH ROLLUP

The result i need is a table like this:

+----+------+--------+---------+
| Id | Name | Cnt Web| Lead Web|
+----+------+------------------+
|  1 | A    | 2      | 0       |
|  2 | B    | 1      | 2       |
|  3 | C    | 1      | 0       | 
|  4 | D    | 0      | 1       |   
+----+------+------------------+

When i try to join the first table with the second or the first with the third, i get the correct result, but i can't get the needed result when i join them all.

Any answer is the most welcomed. Thank you in advance.

Erjon
  • 923
  • 2
  • 7
  • 32
  • Possible duplicate of [Strange duplicate behavior from GROUP\_CONCAT of two LEFT JOINs of GROUP\_BYs](https://stackoverflow.com/questions/45250646/strange-duplicate-behavior-from-group-concat-of-two-left-joins-of-group-bys) – philipxy Nov 11 '17 at 00:22
  • Why does Lead web have a value of 1 for ID 4, shouldn't it be 2 as there are 2 records in the lead table for D? – SE1986 Nov 11 '17 at 00:46
  • @SEarle1986 Because one is on 2017-11-09 and the other one on 2017-11-10, and i need only those in 2017-11-09, as i explained in the slq code above. – Erjon Nov 11 '17 at 00:58
  • OK, updated answer now – SE1986 Nov 11 '17 at 01:04

1 Answers1

0

Here's a solution using correlated sub-queries

SELECT  u.Id,
        u.Name,
        (SELECT COUNT(Name) FROM cnt WHERE Name = u.name AND type = 'Web' AND Date = '2017-11-09') AS cnt_web,
        (SELECT COUNT(Name) FROM lead WHERE Name = u.name AND type = 'Activation' AND Date>= '2017-11-09' AND Date< '2017-11-10') AS cnt_lead
FROM    users u
WHERE   u.Status = 'Aktiv' AND u.Role = 'Op'
SE1986
  • 2,534
  • 1
  • 10
  • 29