updated 2020-08-16
note:
“Using comma separated lists in a database field is an anti-pattern and should be avoided at all costs.”
“properly normalize the table structure”
“I strongly advise that you fix your current database structure so you are not storing the data in a comma separated list.”
– from www.stackoverflow.com
If you cannot alter your table, then you should be able to query using FIND_IN_SET:
example; a list of picture id’s in a post
SELECT po.id, po.header, po.text, po.pic_id_list,
pi.id pi.name, pi.url
FROM posts AS po
JOIN pics AS pi
ON FIND_IN_SET(pi.id, po.pic_id_list)
2nd example [from moodle]:
each course section can have several modules . . . the module id’s are in
a comma separated list in the cs.sequence field-column
SELECT cs.id, cs.course, cs.section, cs.sequence,
cs.visible AS cs_visible,
cm.id AS cm_id, cm.module, cm.instance,
cm.visible AS cm_visible
FROM mdl_course_sections AS cs
JOIN mdl_course_modules AS cm
ON FIND_IN_SET(cm.id, cs.sequence)
WHERE cs.course BETWEEN 120 AND 130
OR cs.course IN ( 132, 134 )
AND LENGTH(cs.sequence) > 0
AND cs.visible = 1
ORDER BY cs.course, cs.section, FIND_IN_SET(cm.id, cs.sequence) ;
“BETWEEN 120 AND 130” includes 120 and 130
each record below has one module (lesson, feedback, certif., quest., etc.)
the name of the module is in its respective table: use left join and case.
SELECT cs.course, cs.section, cs.sequence,
cm.id AS cm_id, cm.module, cm.instance,
l.id AS lessonid,
ROUND((l.completiontimespent /60), 0) AS lsntime,
CASE WHEN cm.module = 11 THEN substring( l.name, 1, 20)
WHEN cm.module = 19 THEN substring( fb.name, 1, 20)
WHEN cm.module = 27 THEN substring(cer.name, 1, 20)
WHEN cm.module = 29 THEN substring(qst.name, 1, 20)
END AS activityName
FROM mdl_course_sections AS cs
JOIN mdl_course_modules AS cm
ON FIND_IN_SET(cm.id, cs.sequence)
LEFT JOIN mdl_lesson AS l
ON ( cm.instance = l.id AND cm.module = 11 )
LEFT JOIN twcc_moodle.mdl_feedback AS fb
ON ( cm.instance = fb.id AND cm.module = 19 )
LEFT JOIN twcc_moodle.mdl_certificate AS cer
ON ( cm.instance = cer.id AND cm.module = 27 )
LEFT JOIN twcc_moodle.mdl_questionnaire AS qst
ON ( cm.instance = qst.id AND cm.module = 29 )
WHERE cs.course IN (18, 20, 233)
AND cs.sequence <> ''
AND cs.visible = 1
AND cm.visible = 1
AND cm.module IN (11, 19, 27, 29)
ORDER BY cs.course, cs.section, FIND_IN_SET(cm.id, cs.sequence) ;
display a unix timestamped order date if not zero (0), otherwise display “0”
SELECT orderid, IF(o.unixtime == 0, 0, FROM_UNIXTIME(o.unixtime) )
FROM orders AS o
to find questions and answers
SELECT l.courseid, SUBSTRING(c.fullname, 1, 30) AS course_name, l.id AS Lid,
SUBSTRING(l.name, 1, 25) AS LessonName,
IF (l.timestamp == 0, 0, SUBSTRING(FROM_UNIXTIME(l.timestamp), 1, 10)) AS avail,
lp.id AS L_PG_id, SUBSTRING(lp.title, 1, 20) AS Page_Title,
la.id AS ans_id, SUBSTRING(la.response, 1, 25) AS ifcorrect,
la.answerformat AS ansf, la.answer
FROM mdl_lesson_answers AS la
JOIN mdl_lesson_pages AS lp
ON la.pageid = lp.id
JOIN mdl_lesson AS l
ON la.lessonid = l.id
JOIN mdl_course AS c
ON l.courseid = c.id
WHERE lp.title LIKE '%Question%'
ORDER BY c.sortorder, l.name, la.id
Case – Sensitive
To make a query on text case sensitive, use the keyword ‘BINARY’
example: country is a 2 char code for country which must be in upper-case.
find the records that are not upper-cased:
WHERE UPPER(country) <> BINARY country
INSTR()
ex: Search for ‘w3s’ in string ‘W3Schools.com’
SELECT INSTR("W3Schools.com", "w3s") AS col FROM customer ;
returns 1
example:
SELECT INSTR(username, 'john') AS col, username
FROM customer
WHERE INSTR(username, 'john') ;
returns
col username
--- --------
1 johnjones
4 maxjohnson
will return 0 if not found
example:
col username
--- --------
0 joejones
Subtracting value from previous row
example data in the table
SN Date Value
2380 2012-10-30 00:15:51 21.01
2380 2012-10-31 00:31:03 22.04
2380 2012-11-01 00:16:02 22.65
2380 2012-11-02 00:15:32 23.11
20100 2012-10-30 00:15:38 35.21
20100 2012-10-31 00:15:48 37.07
20100 2012-11-01 00:15:49 38.17
20100 2012-11-02 00:15:19 38.97
20103 2012-10-30 10:27:34 57.98
20103 2012-10-31 12:24:42 60.83
data in the table plus the “added” column, calculated, see below
SN Date Value added
2380 2012-10-30 00:15:51 21.01 0
2380 2012-10-31 00:31:03 22.04 1.03
2380 2012-11-01 00:16:02 22.65 0.61
2380 2012-11-02 00:15:32 23.11 0.46
20100 2012-10-30 00:15:38 35.21 0
20100 2012-10-31 00:15:48 37.07 1.86
20100 2012-11-01 00:15:49 38.17 1.1
20100 2012-11-02 00:15:19 38.97 0.8
20103 2012-10-30 10:27:34 57.98 0
20103 2012-10-31 12:24:42 60.83 2.85
1st select [using mysql @xxx variables]
select
EL.SN,
EL.Date,
EL.Value, --remove duplicate alias
if( @lastSN = EL.SN, EL.Value - @lastValue, 0000.00 ) as added,
@lastSN := EL.SN,
@lastValue := EL.Value
from
EnergyLog EL,
( select @lastSN := 0,
@lastValue := 0 ) SQLVars
order by
EL.SN,
EL.Date
2nd select
SELECT l.sn,
l.date,
l.value,
l.value - (SELECT value
FROM energylog x
WHERE x.date < l.date
AND x.sn = l.sn
ORDER BY date DESC
LIMIT 1) added
FROM energylog l;
see stackoverflow.com/questions/13196190/mysql-subtracting-value-from-previous-row-group-by
for other methods, selects.
Sub-Queries
When grouping, “order by” takes effect after the grouping has been done and within any group, the first, oldest, record is taken and it goes on to find the next group.
If you want a specific record within a group other than the first (oldest) you will have to order the records before arriving at the “GROUP BY” statement.
For instance, if you want the last (most recent) record,
from a table “customers” where “id” is the auto-incremented key and there is “name” field you want to group by
your query might look like this:
SELECT *
FROM (SELECT *
FROM customers
ORDER BY id DESC) AS x
GROUP BY name
another method is a 2nd join on the table:
SELECT p1.postid
FROM Posts p1 LEFT JOIN posts p2
ON (p1.userid = p2.userid AND p1.postid < p2.postid)
WHERE p2.postid IS NULL AND p1.userid = 20860;
with this next example there will be a grade for every lesson taken – and we will always get the first grade
on the 1st lesson, and we will only get students who have started and gotten at least 1 grade
(on the 1st lesson).
SELECT u.firstname, u.lastname, l.course, lg.lesson
FROM mdl_user AS u
JOIN mdl_lesson_grades AS lg
JOIN mdl_lesson AS l
ON lg.lessonid = l.id
WHERE u.id > 16275
GROUP BY u.id
ORDER BY u.lastname ;
If you are ordering a select by a single (auto) indexed field, you can match each record to its previous or next record.
previous:
SELECT f1.id, f2.id
FROM foo AS f1
WHERE f1.id = (SELECT max(f2.id)
FROM foo AS f2
WHERE f2.id < f1.id)
next:
SELECT f1.id, f2.id
FROM foo AS f1
WHERE f1.id = (SELECT min(f2.id)
FROM foo AS f2
WHERE f2.id > f1.id)
Range:
NOTE
WHERE userid IN (10,11,12,13,14,15)
is the same as
WHERE userid BETWEEN 10 AND 15
*or* to exclude the range:
WHERE userid NOT BETWEEN 10 AND 15
or
WHERE userid NOT IN (10,11,12,13,14,15)
fill in the blank: (name can be found in several records without it’s corresponding value)
UPDATE data_table dt1
JOIN data_table dt2
SET dt1.VALUE = dt2.VALUE
WHERE dt1.NAME = dt2.NAME
AND dt1.VALUE = ''
AND dt2.VALUE <> ''
[ != will often work instead of <> but is not an ISO standard ]
example:
this
ID | NAME | VALUE |
—————————-
1 | Test1 | VALUE1 |
2 | Test2 | VALUE2 |
1 | Test2 | |
4 | Test1 | |
1 | Test3 | VALUE3 |
becomes this
ID | NAME | VALUE |
—————————-
1 | Test1 | VALUE1 |
2 | Test2 | VALUE2 |
1 | Test2 | VALUE2 |
4 | Test1 | VALUE1 |
1 | Test3 | VALUE3 |
an IF statement: one field or the other
example: use the greater-more recent date-time to sort by
$orderby = ' IF(timemodified > lastaccess,
timemodified, lastaccess ) DESC, ' ;
SELECT IF( substring(ca.timeca, 1, 10) >=
substring(sc.timesc, 1, 10),
ca.timeca, sc.timesc ) AS thetime,
UPDATE field if-if-elseIF conditions
SET A = ‘1’ IF A > 0 AND A < 1
SET A = ‘2’ IF A > 1 AND A < 2
WHERE A IS NOT NULL;
UPDATE table
SET A = IF(A > 0 AND A < 1, 1, IF(A > 1 AND A < 2, 2, A))
WHERE A IS NOT NULL;
OR
UPDATE Table
SET A = CASE
WHEN A > 0 AND A < 1 THEN 1
WHEN A > 1 AND A < 2 THEN 2
END
WHERE (A > 0 AND A < 1) OR (A > 1 AND A < 2)
more examples:
UPDATE table
SET
CASE
WHEN condition1 = true
THEN field = 1
WHEN condition2 = true
THEN field = 2
END
WHERE (condition)
UPDATE prices
SET final_price= CASE
WHEN currency=1 THEN 0.81*final_price
ELSE final_price
END
UPDATE multiple tables with one query
UPDATE table1
JOIN table2
ON table1.id = table2.id
SET table1.col1 = a,
table2.col2 = b
WHERE table1.col1 = b ;
OR
$queryup = 'UPDATE table1 SET a=b WHERE c;
UPDATE table2 SET a=b WHERE d;
UPDATE table3 SET a=b WHERE e; ';
$result = mysqli_query($socket, $queryup);
either an update or an insert – in 1 sql statement
INSERT INTO logs_tbl (site_id, adate, hits)
VALUES (1,"2004-08-09", 15)
ON DUPLICATE KEY
UPDATE hits=hits+15;
note:
“INSERT… ON DUPLICATE KEY UPDATE…” can also use a compound (unique) key to check for duplication. This is very userful. For example:
If you have a log table to log hits to different websites daily, with “site_id”-s and “time” fields, where neither of them are primary keys, but together they are unique, then you can create a key on them, and then use “…ON DUPLICATE KEY…”
When using the INSERT … ON DUPLICATE KEY UPDATE statement, the returned value is as follows:
1 for each successful INSERT.
2 for each successful UPDATE.
For example, if you insert 5 rows with this syntax, and 3 of them were inserted while 2 were updated, the return value would be 7:
((3 inserts * 1) + (2 updates * 2)) = 7.
The return value may at first appear worrisome, as only 5 rows in the table were actually modified, but actually provides more information, because you can determine the quantities of each query type performed from the return value.
a “UNIX” timestamp – in the UNIX format is the number of seconds since 1-1-1970 . . . a 10 didget number.
ex: FROM_UNIXTIME(1473475590) ## = 2016-09-09 22:46:30
to display it in a readable format (ex: m-d-y h:m:s OR y-m-d h:m:s, etc) use FROM_UNIXTIME():
FROM_UNIXTIME(unix_timestamp)
or
FROM_ UNIXTIME (unix_timestamp, '%Y %D %M %h:%i:%s %x')
select all log records older than 30 days
SELECT id, userid, ip, timecreated, FROM_UNIXTIME(timecreated)
FROM log_table
WHERE timecreated < UNIX_TIMESTAMP(CURDATE() - INTERVAL 30 DAY )
ORDER BY timecreated DESC ;
then delete them
DELETE
FROM log_table
WHERE timecreated < UNIX_TIMESTAMP(CURDATE() - INTERVAL 30 DAY ) ;
Display subtotals by date out of a timestamp field
SELECT substring(date_purchased, 1, 10) AS saledate,
SUM(order_total) AS subttl
FROM orders WHERE date_purchased > "2010-01-01 00:00:00"
GROUP BY saledate
HAVING subttl > 3500
ORDER BY subttl DESC
then take the sub-totals by day and display the Average sub-total over a month range . . .
like daily subtotals averaged each month – (sorted by date {salemonth} or by sub-total {avettl})
SELECT substring(date_purchased, 1, 7) AS salemonth,
SUM(order_total) /
COUNT(DISTINCT substring(date_purchased, 1, 10))
AS avettl
FROM orders
WHERE date_purchased > "2009-01-01 00:00:00"
GROUP BY salemonth
ORDER BY salemonth DESC
SELECT query return 1 selected row from each group:
SELECT id, product_name, store_id
FROM product_table
GROUP BY store_id
ORDER BY id.
if you want to select a certain record from each group,
for example, the newest, the largest “id” in the group:
SELECT a.id, a.product_name, a.store_id
FROM tableName AS a
JOIN ( SELECT store_id, MAX(id) AS max_id
FROM tableName
GROUP BY store_id ) AS b
ON a.store_id = b.store_id
AND a.id = b.max_id
to display an elapsed time, a difference in two times, as days, hours, minutes, and seconds:
CONCAT(IF((endtime - starttime) >= 86400,
CONCAT( (endtime - starttime) DIV 86400, ' days '), ' '),
IF((endtime - starttime) >= 3600,
CONCAT( (endtime - starttime) DIV 3600, ' hrs '), ' '),
IF( MOD(endtime - starttime, 3600) >= 60,
CONCAT( MOD(endtime - starttime, 3600) DIV 60, ' min ' ), ' '),
IF( MOD(endtime - starttime, 60) > 0 ,
CONCAT(MOD(endtime - starttime, 60), ' sec '), ' ')
) AS hrs_min_secs
A UNION of (3) Queries
you can “Union” any number of queries that all have the same number of fields.
you can have an “Order By” only at the end of the last query and it will order the entire union
on a field that must be common to all the queries.
ex:
use mdl_backup ;
SELECT userid, lessonid, id, starttime, lessontime,
startdate, FROM_UNIXTIME(lessontime) AS lessondate
FROM mdl_lesson_timer
WHERE userid = 13
UNION
SELECT userid, lessonid, id, timeseen AS starttime,
pageid, correct, ts
FROM mdl_lesson_attempts
WHERE userid = 13
UNION
SELECT userid, lessonid, id, completed AS starttime,
grade, late, ts
FROM mdl_lesson_grades
WHERE userid = 13
ORDER BY lessonid, id ;
this union can be ordered by lessonid since it is common to all 3 (5) queries.
the way it is, unsorted, you can put a heading over the 2nd and 3rd query (3rd & 5th)
ORDER BY lessonid
SELECT m.name as movie_title
, CONCAT(c1.name, if(c2.name IS NULL,'',', '), ifnull(c2.name,'')) as categories
FROM movies m
LEFT JOIN categories c2 ON
(replace(substring(substring_index(m.categories, ',', 2),
length(substring_index(m.categories, ',', 2 - 1)) + 1), ',', '') = c2.id)
INNER JOIN categories c1 ON
(replace(substring(substring_index(m.categories, ',', 1),
length(substring_index(m.categories, ',', 1 - 1)) + 1), ',', '') = c1.id)