How do I leave duplicate results in this join query?
I have a union query returning these results:
My lessons taught:
2017-04-02 : 10:00:00 - 12:00:00
2017-04-02 : 15:00:00 - 16:30:00
2017-04-02 : 17:00:00 - 18:00:00
2017-04-02 : 18:10:00 - 19:10:00
2017-04-03 : 10:00:00 - 12:00:00
2017-04-04 : 10:00:00 - 12:00:00
2017-04-05 : 17:45:00 - 18:45:00
2017-04-08 : 08:50:00 - 10:20:00
2017-04-08 : 10:30:00 - 12:00:00
2017-04-08 : 17:30:00 - 18:30:00
...
Is it possible to show each date only once using sql on MySQL 5.7 server? Or does it need to be done later using php? The results look like this because you can teach several lessons on one day, but one lesson belongs to only one day.
Like this: My lessons taught:
2017-04-02 : 10:00:00 - 12:00:00
15:00:00 - 16:30:00
17:00:00 - 18:00:00
18:10:00 - 19:10:00
2017-04-03 : 10:00:00 - 12:00:00
2017-04-04 : 10:00:00 - 12:00:00
2017-04-05 : 17:45:00 - 18:45:00
2017-04-08 : 08:50:00 - 10:20:00
10:30:00 - 12:00:00
17:30:00 - 18:30:00
...
here is the request. I tried making a subquery to join the date, but I can't seem to get it to work.
SELECT
lessons_date,
start_times,
end_times
FROM lessons
JOIN lesson_date
ON id_lessons_date = lesson_date_id_lessons_date
JOIN start_time
ON id_start_times = start_time_has_end_time_start_time_id_start_times
JOIN end_time
ON id_end_times = start_time_has_end_time_end_time_id_end_times;
source to share
A database not used to display the data, How you want to represent it on a web page, so you need to find a way to retrieve it from the database, then depending on the way you can present it.
SELECT
lessons_date,
group_concat(start_times SEPARATOR ',') start_times,
group_concat(end_times SEPARATOR ',') end_times
FROM lessons
JOIN lesson_date
ON id_lessons_date = lesson_date_id_lessons_date
JOIN start_time
ON id_start_times = start_time_has_end_time_start_time_id_start_times
JOIN end_time
ON id_end_times = start_time_has_end_time_end_time_id_end_times;
GROUP BY lessons_date
Then in yours PHP
add something like:
$start_times = explode(",", $row["start_times"]); // array of times
for ($time as $start_times) {
echo $time;
}
source to share
This isn't exactly what you're looking for, and perhaps a more elegant way to do it, but here's an idea for you:
SELECT
CreatedOn =
CASE
WHEN name is null
THEN CreatedOn
ELSE null
END
, Name
FROM (
select distinct
createdon
,null as name
,createdon as c2
FROM account
UNION
SELECT
a.createdon
,a.name
,a.createdon as c2
FROM account a
JOIN account b on a.createdon = b.createdon
) x
ORDER BY c2, name
Returns this:
You might want to consider formatting it in PHP ...
source to share
You may try. This will give an accurate result.
SELECT
temp.lessons_date ,
GROUP_CONCAT(start_times SEPARATOR '<br/>') ,
GROUP_CONCAT(end_times SEPARATOR '<br/>') FROM (
SELECT
lessons_date,
start_times,
end_times
FROM lessons
JOIN lesson_date
ON id_lessons_date = lesson_date_id_lessons_date
JOIN start_time
ON id_start_times = start_time_has_end_time_start_time_id_start_times
JOIN end_time
ON id_end_times = start_time_has_end_time_end_time_id_end_times ORDER BY temp.lessons_date
) temp GROUP BY temp.lessons_date
source to share