MySQL: Some complex syntax

February 17, 2014

updated 2016-10-17

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:

SELECT po.id, po.header, po.text, po.pi_id_list,
pi.id pi.name, pi.url
FROM posts AS po
JOIN pics AS pi
ON FIND_IN_SET(pi.id, po.pi_id_list)

2nd example [from moodle]:

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


 

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

 

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

 

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 != ''

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.
the first record in each group will be SELECTed

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)

Leave a Reply

Your email address will not be published. Required fields are marked *

We try to post all comments within 1 business day