Hello I am facing hard time trying to realized this task. The problem is that I am not sure in which way this have to be proceeded and couldn't find tutorials or information about realizing this type of task.
The question is I have 2 tables and one connecting table between the two of them. With regular query usually what is displayed is the table header which is known value and them then data. In My case I have to display the table horizontally and vertically since the header value is unknown value.
Here is example of the DB
Clients:
+--------+------ +
| ID | client|
+--------+------ +
| 1 | Sony |
| 2 | Dell |
+--------+------ +
Users:
+--------+---------+------------+
| ID | name | department |
+--------+--------+-------------+
| 1 | John | 1|
| 2 | Dave | 2|
| 3 | Michael| 1|
| 4 | Rich | 3|
+--------+--------+-------------+
Time:
+--------+------+---------------------+------------+
| ID | user | clientid | time | date |
+--------+------+---------------------+------------+
| 1 | 1 | 1 | 01:00:00 | 2017-01-02 |
| 2 | 2 | 2 | 02:00:00 | 2017-01-02 |
| 3 | 1 | 2 | 04:00:00 | 2017-02-02 | -> Result Not Selected since date is different
| 4 | 4 | 1 | 02:00:00 | 2017-01-02 |
| 5 | 1 | 1 | 02:00:00 | 2017-01-02 |
+--------+------+---------------------+------------+
Result Table
+------------+--------+-----------+---------+----------+
| Client | John | Michael | Rich | Dave |
+------------+--------+-----------+---------+----------+
| Sony |3:00:00 | 0 | 2:00:00 | 0 |
+------------+--------+-----------+---------+----------+
| Dell | 0 | 0 | 0 | 2:00:00 |
+------------+--------+-----------+---------+----------+
First table Clients Contains information about clients.
Second table Users Contains information about users
Third Table Time contains rows of time for each users dedicated to different clients from the clients table.
So my goal is to make a SQL Query which will show the Result table. In other words it will select sum of hours which every user have completed for certain client. The number of clients and users is unknown. So first thing that have to be done is Select all users, no matter if they have hours completed or not. After that have to select each client and the sum of hours for each client which was realized for individual user.
The problem is I don't know how to approach this situation. Do I have first to make one query slecting all users then foreach them in the table header and then realize second query selecting the hours and foreaching the body conent, or this can be made with single query which will render the whole table.
The filters for select command are:
WHERE MONTH(`date`) = '$month'
AND YEAR(`date`) ='$year'
AND u.department = '$department'
Selecting single row for tume SUM is:
(SELECT SUM( TIME_TO_SEC( `time` ) ) FROM Time tm
WHERE tm.clientid = c.id AND MONTH(`date`) = '$month' AND YEAR(`date`) ='$year'
This is the query to select the times for a user , here by my logic this might be transformed with GROUP BY c.id (client id), and the problem is that it have to contains another WHERE clause which will specify the USER which is unknown. If the users was known value was for example 5, there is no problem to make 5 subsequent for each user WHERE u.id = 1, 2, 3 etc.
So here are the 2 major problems how to display in same query The users header and them select the sum of hours for each client corresponding the user.
Check out the result table hope to make the things clear.
Any suggestion or answer which can come to resolve this situation will be very helpful.
Thank you!
