how to check if value from another column is present in every date -mysql?

user_898042 0
Viewed 45

I have a table:

date       id    count(sub_id)
2016-03-01 2       1 
2016-03-02 650     1 
2016-03-03 2       1 
2016-03-04 2697    2 
2016-03-05 2       4 
2016-03-06 2697    3
2016-03-07 1000    2 
2016-03-08 2       3 
2016-03-09 2697    3

the dates go from 1st march 2016 to 15 march 2016.

i need to check what id is present in every date - from 1 to 15 march. i.e what user submitted data on every day

so far I have this

select submission_date, id, count(submission_id) 
from submissions  
group by submission_date, id
having count(submission_id) >= 1

but this only shows the dates and users and how many submissions they made on every day - for example 2.

date datetime mysql
2 Answers

You must group by id and check in the HAVING clause if the distinct number of dates of each id is equal to the distinct number of dates in the table:

select id
from submissions  
group by id
having count(distinct submission_date) = (select count(distinct submission_date) from submissions)

If there are no duplicate id, submission_date combinations you can change to:

having count(submission_date) = (select count(distinct submission_date) from submissions)

and if also there are no null dates:

having count(*) = (select count(distinct submission_date) from submissions)
user_104988280

You should GROUP BY date

CREATE TABLE table1 (
  `date` DATE,
  `id` INTEGER
);

INSERT INTO table1
  (`date`, `id`)
VALUES
  ('2016-03-01', '2'),
  ('2016-03-02', '650'),
  ('2016-03-03', '2'),
  ('2016-03-04', '2697'),
  ('2016-03-03', '2697'),
  ('2016-03-05', '2'),
  ('2016-03-06', '2697'),
  ('2016-03-07', '1000'),
  ('2016-03-08', '2'),
  ('2016-03-09', '2697');

SELECT `date`, GROUP_CONCAT(DISTINCT `id` ORDER BY `id`)
FROM table1
GROUP BY `date`
ORDER BY `date`

date       | GROUP_CONCAT(DISTINCT `id` ORDER BY `id`)
:--------- | :----------------------------------------
2016-03-01 | 2                                        
2016-03-02 | 650                                      
2016-03-03 | 2,2697                                   
2016-03-04 | 2697                                     
2016-03-05 | 2                                        
2016-03-06 | 2697                                     
2016-03-07 | 1000                                     
2016-03-08 | 2                                        
2016-03-09 | 2697

db<>fiddle here

user_51935360
Related