Home > Enterprise >  MYSQL do not show duplicate ID in INNER JOIN
MYSQL do not show duplicate ID in INNER JOIN

Time:03-31

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

enter image description here

Here's the result of the statement above

enter image description here

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

  • Related