Stack Overflow
Stack Overflow

Questions tagged [mysql-dependent-subquery]

Use this tag for questions related to a dependent (correlated) subquery in MySQL.

A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query.

as stated in MySQL: 13.2.10.7 Correlated Subqueries.

Question tagged with talk about optimization and performance issues with this kind of queries, in addition to getting them right, etc. .

31 questions
492
votes
4 answers

What is the error "Every derived table must have its own alias" in MySQL?

I am running this query on MySQL SELECT ID FROM ( SELECT ID, msisdn FROM ( SELECT * FROM TT2 ) ); and it is giving this error: Every derived table must have its own alias. What's causing this error?
silverkid
  • 9,291
  • 22
  • 66
  • 92
14
votes
3 answers

MySQL query with dependent subquery takes too long

I need an SQL guru to help me speed up my query. I have 2 tables, quantities and prices. quantities records a quantity value between 2 timestamps, 15 minutes apart. prices records a price for a given timestamp, for a given price type and there is a…
neilcrookes
  • 4,203
  • 2
  • 21
  • 27
4
votes
2 answers

MySQL : Dependent Sub Query with NOT IN in the WHERE clause is very slow

I am auditing user details from my application using open Id login .If a first time a user is login a OPEN ID we consider as signup . I am generating audit signin report using this details . Sample Table…
RBK
  • 77
  • 2
  • 8
3
votes
2 answers

Do all subqueries in CASE..WHEN get executed, even if only one condition is met?

Say I have a query with subqueries in CASE...WHEN...THEN like this (simplified, as the real query I'm working on is little harder on the eyes): SELECT CASE WHEN (subquery1) = 1 THEN (subquery2) WHEN (subquery1) > 1 AND (subquery3) =…
Tien Phan
  • 55
  • 9
3
votes
1 answer

UPDATE with SELECT subquery runs extremely slowly on MySQL 5.7 (but was fine on 5.5)

Thank you all in advance. I have encountered an issue in upgrading my database from MySQL 5.5 to 5.7 that has me completely and totally confounded. The upgrade was not done using mysqldump or similar, but as a rebuild from several tab separated…
convensive
  • 53
  • 5
3
votes
4 answers

Optimize MySQL query with dependent sub-query

I need to find a way to eliminate a dependent sub-query. I have a table of articles that can have multiple languages. The simplified table structure is as follows: id, title, language, translation_set_id 1 A en 0 2 B en 2 3 B_ru ru 2 4 C en…
alcroito
  • 401
  • 3
  • 13
2
votes
2 answers

MySql - SubQuery is not working properly

BubQuery always return the empty result if the query is correct. 1st Query: SELECT * FROM `user` WHERE user_age IN(1,22,34); Result: 2nd Query: SELECT GROUP_CONCAT(user_age_list) AS user_age FROM `user_detail` WHERE id='1'; Result: I am…
Ramalingam Perumal
  • 1,367
  • 2
  • 17
  • 46
2
votes
1 answer

How to prevent dependant subqueries within CASE WHEN x THE (subquery)

I have a very complex query which uses some subqueries within a CASE statement. For this question the complete query isn't needed and would just prevent people from getting into the problem quick. So this post uses pseudocode to work with. If wanted…
user2429266
  • 390
  • 1
  • 3
  • 19
2
votes
1 answer

How do I improve the performance of a MySQL query that has a dependent subquery that isn't?

I am working with a table in MySQL that defines a tree hierarchy using the "adjacency list" method, which should be fine for my purposes. I need to compute the maximum of some value over all children using the (fast) query SELECT MAX(N.someValue) AS…
gdoug
  • 715
  • 1
  • 5
  • 16
1
vote
1 answer

MySQL Group By and Count Performance

I have two tables Export Name Type Collation Attributes Null Default Extra id int(10) utf8_unicode_ci UNSIGNED No None AUTO_INCREMENT email varchar(150) utf8_unicode_ci No…
Aleksandr Korsukov
  • 31
  • 2
1
vote
1 answer

select unique values from column but order based on another

I need a unique list of parent_threads based on the desc order of postID, postID is always unique but often the parent_thread field is the same for multiple posts. So what i need is a list of posts in order they were replied to. so for example in…
foofighta
  • 13
  • 2
1
vote
1 answer

how to get select sub-query in which another table whole data get in json format

Table 1 Table 2 I want this kind of output needed in Format + My Current Query this SELECT *, (SELECT id FROM tb1 where id = 1) as data_json FROM `tb5` i want get table tb1 whole data in one column like that id | col1 | col2 | col3 | col4 |…
Hitesh
  • 233
  • 2
  • 7
1
vote
1 answer

SQL query with subquery

My data is like this: data1_qqq_no_abc_ccc data1_qqq_abc_ccc data2_qqq_no_abc_ccc data2_qqq_abc_ccc data3_qqq_no_abc_ccc data4_qqq_no_abc_ccc data4_qqq_abc_ccc ... Now I want to get the fields where data has substring _no_abc_ccc, but doesn't have…
Harsh Sharma
  • 10,942
  • 2
  • 18
  • 29
1
vote
1 answer

mysql Retrieve and manipulate current row and previous row from table

I have looked several places, and maybe I'm just not phrasing the search correctly. I have found similar questions, but none of them answer the question. I have a table of Sight Inventories (where users walk through the storage area and physically…
Rod
  • 13
  • 4
1
vote
1 answer

Reference of table in the parent query from a sub subquery

I want to perform a list of users and extract a list of contacts done by each one, grouped by type of item contacted, in one field. Something like this: user_id, user_name, user_contact_views 100, john, 'a=21,b=22,c=3' 101, mary, 'a=53' 102, jack,…
Emilio Nicolás
  • 2,554
  • 5
  • 22
  • 29
1
2 3