All Questions
19 questions
1
vote
0
answers
68
views
Inconsistent result when running same query on slightly distinct MariaDB versions
I'm investigating the difference in the results given by the same SQL query on two servers running slightly different versions of MariaDB (10.5 and 10.11).
Test query is:
SELECT a.Name
FROM Artist a ...
- 111
1
vote
2
answers
93
views
How do I optimize this query that gets the newest entry of each group?
I have a query, that finds the newest item of each group based on the ID returned by a subquery. The problem is, that the table has a bit over 5000 entries and even with only this amount of entries ...
- 13
1
vote
1
answer
476
views
How to return list of content from subquery for the main query?
I have a create that have sub query. To show as example, here is a query that has similarity with my own. Personally I have more table and more arguments on table (The subquery have left join for ...
- 181
1
vote
1
answer
162
views
Combining Multiple queries from the same table inrto 1 query
I am looking to find a way to achieve the below...
I have tried using Joins and SubQueries but no joy as yet...
I have a table that keeps track of wins/loses/draws as so
Date
WLD
01/01/23
Win
05/01/...
- 43
1
vote
1
answer
502
views
Inconsistent results with MySQL, query never ends on MariaDB
We have some really strange behaviour on MySQL (8.0.29) that we can't explain.
We have 2 tables + 1 link table in-between, illustrated by this schema :
We run a query whereby we need to fetch the id ...
- 107
0
votes
0
answers
61
views
How to avoid subquery duplication? Getting multiple rows of subquery
I need to create a view like this:
CREATE OR REPLACE VIEW bayer_pss_dashboard AS
SELECT
m.machine_id AS machine,
t.name AS time_interval,
(
SELECT SUM(TIMESTAMPDIFF(MINUTE, me....
- 101
0
votes
2
answers
143
views
Subquery into join issue
I'm trying hard as I can to transform my query to get rid of subqueries. I know its better to not use subqueries, but my first reason to change this query is because my ORM (Doctrine) isn't able to ...
0
votes
2
answers
165
views
I have a correct subquery that returns results but when I use it as an "IN" clause I get error 1064
This must be a very simple problem, the subquery returns results alone but the compound query reports:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to ...
- 11
0
votes
2
answers
1k
views
LEFT JOIN subquery fails in the presence of an EXISTS clause in the main query
I have this query:
SELECT c.id contact_id, engagement.sent
FROM contact c
LEFT JOIN (
/* ➊ Inner query */
SELECT eq.contact_id contact_id, COUNT(*) sent
FROM mailing_job j
INNER JOIN ...
- 427
0
votes
2
answers
1k
views
MariaDB: Insert records (list of tuples) into table if not exists
Currently I have several sql queries that look like this:
INSERT INTO `test` (col1, col2)
SELECT * FROM (SELECT '1','2') AS tmp
WHERE NOT EXISTS (
SELECT * FROM `test`
WHERE col1='1' AND col2='...
0
votes
1
answer
1k
views
SQL - Select everything after a certain row
After the following query:
SELECT
my_table.ID,
my_table.DATETIME,
my_table.VALUE,
diagnostics_keys.type as type,
diagnostics_keys.key,
diagnostics_keys.unit,
...
0
votes
1
answer
1k
views
MariaDB, SEQUENCE engine, dynamic upper bound
I'd like to generate a sequence in MariaDB starting at 1 and ending with whatever is the largest id in some other table. That other table has roughly this schema:
create table main (
id integer ...
- 145
0
votes
1
answer
145
views
Cannot get a user function to co-operate with a subquery in mariadb
The following is a demo of an issue we are having with a function and a subquery. I'm interested to know if it is a bug or if there is a reasonable explaination why it is failing.
Notes:
Problem ...
- 111
-2
votes
1
answer
59
views
Subquery and subselection for Count/Number doesnt work
for example, for company (3) and his partners, I need the number of events with payouts. But i get only the number for one partner, im my example i have
i have 3 tables (t_company, t_partner, t_event,...
- 1
0
votes
1
answer
430
views
Optimize multiple group_concat
Just wondering if this query could be optimized or if sub-queries would be better?
select t.entry_id,
t.title,
d.field_id_249 as unique_id,
d.field_id_577 as keywords,
d.field_id_681 AS video,
...
- 101
1
vote
1
answer
37
views
Selecting entries with the same count as in another table
I'm using a SELECT statement to select multiple IDs and than use
GROUP BY ID
HAVING COUNT(*) = someNumber
to only have the IDs with a certain count.
What I however actually want to do is to select ...
- 45
2
votes
3
answers
1k
views
2 fast SQL queries get slow when bunched as subquery on a very large table
I'm trying to use a subquery to speed up a query on a very large table right now 30 million row it most likely going to end up close to 1 billion rows.
My table lnmntr is as follow
Lnmntr_ndx: ...
user6427891
3
votes
1
answer
491
views
MariaDB subquery cache scope and settings
I am migrating an application from MySQL 5.6 to MariaDB 10. One major new feature of MariaDB is the subquery cache. I am trying to determine the expected effect this cache will have on our ...
- 167
-1
votes
2
answers
1k
views
Optimize query or split in two queries and process result separately?
I have this DB schema:
As you may notice already there is no relationship between tables and won't be on the future. If I run the following queries I will get a lot of rows per table:
SELECT COUNT(*)...
- 1,888