Skip to content

Tag: mysql-8.0

SQL Query: Search Across Multiple Fields in JOINed Tables

SQL Version: MySQL 8.0 or SQL Server SQL Fiddle: 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

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

Getting values from the first row, last row, and an aggregation in MySQL Window function

For a marketing related analysis I need to provide data on the first- and last-touchpoint and of the number of total interactions with our website. A simplified version of our interaction table looks like this: Our current approach looks like this: Currently, we observe that the runtime scales approximately linear with our data, which will become infeasible to compute soon.

mysql Cumulative sum for each unique ID in each sale:

I have trouble adding and accumulating my amounts per id, I use this solution but it doesn’t work and it adds up all the ids, I use this: any solution? ineed this For version 5.7 ? Answer You can use a SELECT statement containing window analytic function instead of an UPDATE statement : acumulado column shouldn’t need to be stored