I am trying to make a function which checks if any date between say Date-range A and B lies between the Date-range C and D. Whenever I try running the function MySQL Workbench always throws an Error Code:2013 Lost connection to MySQL Server during query. I am assuming this error is arising due to the loop running forever (please correct
Tag: mysql-8.0
SQL Query: Search Across Multiple Fields in JOINed Tables
SQL Version: MySQL 8.0 or SQL Server SQL Fiddle: https://www.db-fiddle.com/f/wcHeXkcynUiYP3qzryoYJ7/6 I have a table of images and a table of tags that link to those images. I want to be able to pass (2) tags to the query and have it select only the images that match BOTH tags. For example, I want to pass November and 2021 and have
select the data of all the students scoring highest marks in each subject group by city
I am using mysql version 8.0.23 the given table is:- and I want to get the complete data of the students having highest marks in each subject from each city — I had used this but still I am not able to see name and id of that student Answer here is one way using window functions:
mysql 8 pivot query should return a non null value
I would like the following pivot query to show value 0 instead of null, Output for the above is, Employee id 122 does not have a Repayment value so the desired output is, dbfiddle Answer I don’t see the need for the second branch of the repayment case. If you want 0 when the category is not available, just else
MySQL – Performance issue while joining most recent
I have two tables, markets (27 records) and histories (~1.75M records, ~67K per market). I need to get every market with its most recent histories record. The solutions I tried work but are incredibly slow. Tables DDL What I tried 1 – Uncorrelated subquery I started with this solution since I used it other times, it takes ~7.5s: EXPLAIN result:
MySQL query to find all child elements
I have two tables : elements : id_element name elem1 CPV elem2 CBO elem3 CPV parenting (Which is referencing elements) id_element id_element_elements elem1 null elem2 elem1 elem3 elem1 elem4 elem2 Is there a query to find all the child elements from a single element in this case ? Looking for something like : id_element id_child elem1 elem2 elem1 elem3 elem2
Possible for a MySQL trigger to silently fail?
I’m pulling my hair out trying to debug something. We have large raw tables where ecommerce data gets added to at high. We have summary tables that are populated via insert, update, and delete triggers which are used when users query from our UI. So, those two tables should “match” which is to say if, for example, SUM(revenue) from the
MySQL use JSON_CONTAINS with a subquery
I’m trying to make a subquery inside JSON_CONTAINS to have a results like this This is what I have tried But since the subery query returns Client instead of “Client” JSON_CONTAINS doesn’t accept the value. ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_contains: “Invalid value.” at position 0. Answer You could just concat the double quotes
Improving the performance of a MySQL left join sub query
I have the following MySQL query which calculates the total number of orders for each month within a given date range e.g. a year. The query works correctly, but the performance is slow (around 250ms). Any ideas on how to rewrite it to make it more efficient? UPDATE: Some have suggested using two left joins, however if I do that
MySQL join dataset on at least X items
My question in a SQL Fiddle. How can I join all elements in table A with all elements in table B via a many-to-many mapping table, even if no relationship exists? My LEFT JOIN solution isn’t giving the results I expect. Details: Given these tables: And this seed data: I need a report like this: The query I have tried