Iv got following tables:
articles
+----+-------------+-----------------------------+--------------+
| ID | ID_group_AG | Title | Date_publish |
+----+-------------+-----------------------------+--------------+
| 1 | 10 | O obrotach sfer niebieskich | 2009-05-07 |
| 2 | 11 | Technologia betonu | 2011-03-21 |
| 3 | 12 | test | 2008-01-13 |
+----+-------------+-----------------------------+--------------+
employee
+----+-----------+-----------+
| ID | Name | Surname |
+----+-----------+-----------+
| 1 | Andrzej | Gacek |
| 2 | Leszek | Ksiazek |
| 3 | Krzysztof | Skibinski |
| 4 | Andrzej | Inny |
+----+-----------+-----------+
articlesGroup
+----+----------+---------------+----------------+
| ID | ID_group | ID_employee | Points |
+----+----------+---------------+----------------+
| 1 | 10 | 1 | 3 |
| 2 | 10 | 3 | 3 |
| 3 | 11 | 1 | 2 |
| 4 | 11 | 2 | 2 |
| 5 | 11 | 4 | 2 |
| 6 | 12 | 4 | 6 |
+----+----------+---------------+----------------+
And following relations:
articles.ID_group_AG => articlesGroup.ID_group
articlesGroup.ID_employee => employee.ID
What I need to do is to print all article points related to a employee, article and publish date, so I use folowing query:
SELECT
p.Name,
p.Surname,
a.Date_publish,
ag.Points
FROM
employee p,
articles a,
articlesGroup ag
WHERE
(ag.ID_group = a.ID_group_AG) AND
(ag.ID_employee = p.ID)
and I get:
+-----------+-----------+--------------+----------------+
| Name | Surname | Date_publish | Points |
+-----------+-----------+--------------+----------------+
| Andrzej | Gacek | 2009-05-07 | 3 |
| Andrzej | Gacek | 2011-03-21 | 2 |
| Leszek | Ksiazek | 2011-03-21 | 2 |
| Krzysztof | Skibinski | 2009-05-07 | 3 |
| Andrzej | Inny | 2011-03-21 | 2 |
| Andrzej | Inny | 2008-01-13 | 6 |
+-----------+-----------+--------------+----------------+
Now lets get to the problem :)
Im using pChart library to make charts.
I want to put on Y axis Points, on X axis all Dates regarding each employee.
So Points array for employee "Andrzej Gacek" will be: [3,2]
for employee "Krzysztof Skibinski" will be: [3]
and Date array (sorted): ["2008-01-13","2009-05-07","2011-03-21"]
I need to add zero points to employee Points array for ex. for "Andrzej Gacek" array should look like: [0,3,2] so Point will correlate with Dates.
How to form a query to add zeroes to points so the output of the query would look like this:
+-----------+-----------+--------------+----------------+
| Name | Surname | Date_publish | Points |
+-----------+-----------+--------------+----------------+
| Andrzej | Gacek | 2009-05-07 | 3 |
| Andrzej | Gacek | 2011-03-21 | 2 |
| Andrzej | Gacek | 2008-01-13 | 0 |
| Leszek | Ksiazek | 2011-03-21 | 2 |
| Leszek | Ksiazek | 2009-05-07 | 0 |
| Leszek | Ksiazek | 2008-01-13 | 0 |
| Krzysztof | Skibinski | 2009-05-07 | 3 |
| Krzysztof | Skibinski | 2011-03-21 | 0 |
| Krzysztof | Skibinski | 2008-01-13 | 0 |
| Andrzej | Inny | 2011-03-21 | 2 |
| Andrzej | Inny | 2008-01-13 | 6 |
| Andrzej | Inny | 2009-05-07 | 0 |
+-----------+-----------+--------------+----------------+
You have to create a Cartesian Product to get the results you are looking for. I've used a CROSS JOIN to get the dates for each employee.
Give this a try:
SELECT DISTINCT E.Name,
E.SurName,
a.Date_Publish,
IFNULL(AG.Points,0) Points
FROM Articles A CROSS JOIN
Employee E LEFT JOIN
ArticlesGroup AG ON ag.ID_group = a.ID_group_AG
AND E.Id = ag.ID_employee
And here is the SQL Fiddle.