mysql- ignore duplicate values from a mysql table

Question

I have a table which stores user login information. Table stores user's login information to a particular system. So, when a user login to a system, it's saved in my table and the status field is set as 1.

This is my table structure:

CREATE TABLE IF NOT EXISTS `login_info` (
  `id` int(11) NOT NULL,
  `staffID` int(11) NOT NULL,
  `logDate` date NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=latin1;

Thing is, some of the user records are replicated and I am trying to avoid them. I mean, a few users log in to the system more than once. So, for a particular day they may have more than one record and for some other day, they won't have any if they didn't log in at all.

I made a sample of the data:

INSERT INTO `login_info` (`id`, `staffID`,`logDate`, `status`) VALUES
(1, 100, '2016-10-01',1),
(2, 100, '2016-10-01',1),
(3, 100, '2016-10-01',1),
(4, 101, '2016-10-01',1),
(5, 101, '2016-10-01',1),
(6, 102, '2016-10-01',1),

(7, 100, '2016-10-02',1),
(8, 101, '2016-10-02',1),
(9, 101, '2016-10-02',1),
(10, 102, '2016-10-02',1),
(11, 103, '2016-10-02',1),

(12, 100, '2016-10-03',1),
(13, 101, '2016-10-03',1),
(14, 102, '2016-10-03',1),
(15, 103, '2016-10-03',1),

(16, 100, '2016-10-04',1),
(17, 100, '2016-10-04',1),
(18, 102, '2016-10-04',1),
(19, 103, '2016-10-04',1);

You know, staff with id 100 checked in 3 times on 2016-10-01 and staff with id 103 didn't login at all.

I'm trying to fetch distinct records for each day, which ain't comin' the way I thought.

Here's a FIDDLE

I tried

select staffID from login_info WHERE logDate >= '2016-10-01' AND status=1;

AND

select logDate from login_info WHERE logDate >= '2016-10-01' AND status=1;

It's not fetching what I am looking for.

This is what I'm trying to achieve:

id    staffID    logDate      status
3     100        2016-10-01     1
5     101        2016-10-01     1
6     102        2016-10-01     1
7     100        2016-10-02     1
9     101        2016-10-02     1
10    102        2016-10-02     1
11    103        2016-10-02     1
12    100        2016-10-03     1
13    101        2016-10-03     1
14    102        2016-10-03     1
15    103        2016-10-03     1
17    100        2016-10-04     1
18    102        2016-10-04     1
19    103        2016-10-04     1

You have a condition specifying the date should be from October 2nd onward - why would you get records from October 1st? — Mureinik
– Mureinik, Commented Oct 5, 2016 at 6:15

rypskar · Accepted Answer · 2016-10-05 06:22:43Z

1

Would the following work for you?

SELECT * FROM login_info 
WHERE logDate >= '2016-10-02' 
AND status=1 
GROUP BY staffId, logDate 
ORDER BY logDate;

If you want to include 2016-10-01, you need to change to logDate >= '2016-10-02

The important part here is the group by which give you data for each users for each day. Order by is only there to return the data in the order you wanted

edited Oct 5, 2016 at 6:22

answered Oct 5, 2016 at 6:17

rypskar

2,08714 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql- ignore duplicate values from a mysql table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related