I'm trying to hide the duplicate u.u_id
in my database table. I tried using the SELECT DISTINCT
statement but it is not hiding. Can someone help me? Thank you!
SELECT DISTINCT u.u_id, l.l_id, u.fname, u.mname, u.lname, u.dept, u.user_type, u.u_grdsec, e.e_name, l.date_added, DATE(l.date_added)
FROM logs l
INNER JOIN users u ON l.u_id = u.u_id
INNER JOIN establishment e ON l.e_id = e.e_id
WHERE DATE(l.date_added) = CURDATE()
ORDER BY l.date_added DESC;
Here is my LOGS table
Here's the result of the statement above
Expected result for the u_id
is something like this
------
| u_id |
------
| 48 |
| 47 |
| 57 |
| 60 | <-- hide the other 60 row
| 71 |
| 66 | <-- hide the other 66 row
| 83 |
| 58 |
| 56 |
| 61 |
| 59 |
| 90 |
| 91 |
| 68 |
------
SOLUTION:
SELECT l.l_id, u.fname, u.mname, u.lname, u.dept, u.user_type, u.u_grdsec, e.e_name, l.date_added, DATE(l.date_added), date_format(l.date_added, '%H:%i') as 'time'
FROM logs l INNER JOIN users u ON l.u_id = u.u_id
INNER JOIN establishment e ON l.e_id = e.e_id
WHERE DATE(l.date_added) = CURDATE()
GROUP BY
date_format(l.date_added, '%H:%i'), l.u_id
ORDER BY l.date_added DESC
CodePudding user response:
Do groub by i.u_id or apply left join