I have three tables, with these fields: classes: class_id | name | grade classes_students: class_id | student_id students: student_id | name Classes has a 1:n relationship with Students, so one …
Tag: mysql
Meaning of “Select tables optimized away” in MySQL Explain plan
What is the meaning of Select tables optimized away in MySQL Explain plan? explain select count(comment_count) from wp_posts; +—-+————-+—————————+———————…
Join 2 Tables and display everything
I have 2 simple tables: table a: id | txt —+—- 0 | aaa 1 | bbb 2 | ccc table b: id | tel —+—- 0 | 000 2 | 111 I am trying to join 2 tables like this: SELECT a.*,b.* FROM a,b …
MySql – Way to update portion of a string?
I’m looking for a way to update just a portion of a string via MySQL query. For example, if I have 10 records all containing ‘string’ as part of the field value (i.e., ‘something/string’, ‘…
sort the “rollup” in group by
I found that the “with rollup” option used with group by is very useful. But it does not behave with “order by” clause. Is there any way to order by the way I want as well as calculate the sub-totals? …
Best way to test if a row exists in a MySQL table
I’m trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE … and check to see if the total is non-zero or is …
Can MySQL replace multiple characters?
I’m trying to replace a bunch of characters in a MySQL field. I know the REPLACE function but that only replaces one string at a time. I can’t see any appropriate functions in the manual. Can I replace or delete multiple strings at once? For example I need to replace spaces with dashes and remove other punctuation. Answer You can
MySQL: select the closest match?
I want to show the closest related item for a product. So say I am showing a product and the style number is SG-sfs35s. Is there a way to select whatever product’s style number is closest to that? …
Combining OUTER JOIN and WHERE
I’m trying to fetch some data from a database. I want to select an employee, and if available, all appointments and other data related to that employee. This is the query: SELECT TA.id, TEI….
Combining UNION and LIMIT operations in MySQL query
I have a Jobs and a Companies table, and I want to extract 20 jobs that meet the following criteria: Jobs only from two (2) named companies There can at most be 10 jobs per company I have tried the following SELECT with UNION DISTINCT, but the problem is that the LIMIT 0,10 applies to the whole result set. I