Mysql select where id in multiple ids

Question

There was one problem and I can not find a solution.

I have table something like this

CREATE TABLE IF NOT EXISTS `system_tests_run` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test_id` int(11) NOT NULL,
  `questions_id` text NOT NULL,
  `run_id` int(11) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_to` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_from` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `class` varchar(64) CHARACTER SET utf8 COLLATE utf8_czech_ci NOT NULL,
  `minutes` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;



INSERT INTO `system_tests_run` (`id`, `test_id`, `questions_id`, `run_id`, `created`, `date_to`, `date_from`, `class`, `minutes`) VALUES
(1, 1, '1', 1, '2014-03-02 16:59:07', '2014-03-02 17:05:00', '2014-03-02 16:58:00', '1,2,3,4,5,6,7,8,9', 15);

and i using this query for load data joined with table users,

SELECT r.date_from, 
       r.date_to, 
       r.id, 
       t.name, 
       t.subject, 
       u.meno, 
       u.priezvisko 
  FROM `system_tests_run` r 
       LEFT JOIN system_tests t 
                 ON t.id = r.test_id 
       LEFT JOIN system_users u 
                 ON u.id = r.run_id 
 WHERE r.class IN (1) 
       AND r.date_from <= NOW() 
       AND r.date_to >= NOW();

This table is for online exam testing, and in field class i store name of class for example (2) , my query working only for 1st class, if had for example in where clause WHERE r.class IN (2) so the command will not work

Query works fine if i use WHERE r.class IN (1,2,3,4,5,6,7,8,9) but i need only one, Because the student has only one class and for the class may be directed test, not all You can not think of any solution suitable for me? Thanks,.

I need select row ONLY ONE VALUE FROM field class. for example 2, and its not working if field has stored data (1,2,3,4,5,6,7,9) ... select * from system_tests_run WHERE class IN (2) .. for example query. — user2982550, Mar 02 '14 at 17:24
Don't understand either. Do you want to get only one result? Order by some parameter, and use `LIMIT 1` to retrieve only the first row. You can use `UNION` or maybe the `COALESCE` function to fill in in case of nonexistent data. Or maybe `GROUP`. Best of all would be to supply an example or, even better, a SQL Fiddle (sqlfiddle.com) — LSerni, Mar 02 '14 at 17:27
Just a guess,add `GROUP BY t.name HAVING COUNT(r.class)=the number of elements in IN` in your case with IN(2) is 1 element — Mihai, Mar 02 '14 at 17:30
Sorry guys my english is very bad i know but im trying to write it, ... i have a system, exam system for teaching students in school, teacher create test, now he need run test, they select classes from multiple selectbox ...and stored in the database(system_test_run classes) as 5,7,9. Everything is ok for students 5. class, if i logged with student 7.class,9.class , result is empty, but cannot be empty... i think i dont need any group and limits, main problem is in WHERE r.class IN (). — user2982550, Mar 02 '14 at 17:32
Create a fiddle the sql language is universal and much more precise.http://sqlfiddle.com/ — Mihai, Mar 02 '14 at 17:38
here is working http://sqlfiddle.com/#!2/9adc37/8 , here is not working query http://sqlfiddle.com/#!2/9adc37/9 — user2982550, Mar 02 '14 at 18:45

score 1 · Accepted Answer · answered Mar 02 '14 at 19:19

1

Use REGEXP,but you should really learn about normalization

SQL FIDDLE

answered Mar 02 '14 at 19:19

Mihai

26,325
7
66
81

1

Thanks you, i read it, im not very good in sql/database, but im learning – user2982550 Mar 02 '14 at 19:26
@user2982550 Also read this http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574 – Mihai Mar 02 '14 at 19:28

Mysql select where id in multiple ids

1 Answers1