CODEWITHSUNDEEP
mysql
Smokey
Smokey

Reputation: 1897

mysql- ignore duplicate values from a mysql table

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

Upvotes: 1

Views: 54

Answers (1)

rypskar
rypskar

Reputation: 2092

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

Upvotes: 1

Related Questions