Select rows with article dates and points (no relation)
Iv got the 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 the following relationship:
articles.ID_group_AG => articlesGroup.ID_group
articlesGroup.ID_employee => employee.ID
I need to do in order to print all the paragraphs of an article related to an employee, the article and publish the date, so I use the following 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 let's get down to the task :)
Im using the pChart library to create charts.
I want to put points on the Y axis, on the X axis of all Dates relative to each employee.
Thus, the point array for the employee "Andrzej Gacek" will be: [3,2]
for an employee of "Krzysztof Skibinski" will be: [3]
and the Date array (sorted): ["2008-01-13", "2009-05-07", "2011-03-21"]
I need to add zero points to the Employees Points array for ex. for the array "Andrzej Gacek" should look like this: [0,3,2] so Point will be correlated with dates.
How to form a query to add zeros to points so that the query result looks 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 |
+-----------+-----------+--------------+----------------+
source to share
You need to create Cartesian products to get the results you are looking for. I used CROSS JOIN
to get dates for each employee.
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 .
source to share