SQL term used to describe when a `SELECT` statement is used as part of a larger SQL statement. The larger statement may be DML and is always found within brackets or parenthesis.

learn more… | top users | synonyms

1
vote
2answers
61 views

Select highest id of each type in mysql

I am creating a database that keeps track of backups on our various servers. The query that I am using actually seems to work for the most part. The only issue is that it isn't getting the highest ...
1
vote
2answers
88 views

Slow subquery in SELECT statement

Having tried many different methods, I can't get my query taking shorter than 40 seconds to run with a dataset of ~11000 rows. The table is a list of hotel bookings, containing (most relevant) ...
0
votes
2answers
40 views

Which query performs better when searching a row where a column has maximum?

Consider a simple table for example purposes: person(name varchar, age int) I have a task to retrieve the oldest person. Which query is better in general for such problems? Query 1: criteria with ...
0
votes
2answers
51 views

Delete the second record where a user has two records

I have a simple database scheme, which due to previously being badly set up has numerous duplicate records. record_table |--------------------------------------------------------| | id | subid | ...
0
votes
2answers
65 views

Using subqueries in delete query

I have a table candidate. It contains two columns name and roll. The values in all three rows of data are AA and 10. Now I want to delete only the second row. How can I do it?
0
votes
2answers
110 views

How to rewrite subquery to return only 1 row

I need to somehow check if query result is NULL, MySQL returns error Subquery returns more than 1 row. How should I rewrite it in order to work correctly please? SELECT DISTINCT user.id, ...
0
votes
2answers
172 views

#1093 - You can't specify target table 'R' for update in FROM clause

As an attempt to answer Difference data between adjacent rows UPDATE Visits R SET Duration = TIMEDIFF( ( SELECT ReqTime FROM Visits N WHERE N.ID > R.ID AND N.Session=R.Session ORDER BY ID ...
0
votes
2answers
30 views

Is my subquery inneficient. It is extremely slow

I have two tables. Member information and login entries. I am simply trying to display the member information table sorted by the last login access date. So i created this query, but it is extremely ...
-1
votes
2answers
55 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 ...
4
votes
1answer
54 views

Changes access method for non-correlated subquery

Oracle 11g R2 Unfortunately our application has per row security "features". We have a query that looks about like this: Bad, slow: SELECT someRow, someOtherRow FROM bigTableA a WHERE EXISTS ( ...
2
votes
1answer
137 views

Minimizing Page Fetches

I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use ...
1
vote
1answer
18 views

mysql - select random rows with max 3 rows per parent id

I currently have a query that returns a maximum of 3 rows per parent_id. I want to enhance the query to return a maximum of 3 random rows per parent_id. This does not work for me at the moment as it ...
1
vote
1answer
205 views

Very slow update with subquery (different tables)

I run this query to update "domains" table based on "log" table: UPDATE domains SET domains.mx_collected = 0 WHERE domains.domain IN (SELECT log.mail as domain_name FROM log WHERE `time` >= ...
0
votes
1answer
35 views

Passing filter params to a sub query

I'd like to create a view from the query below, but the query contains a sub query which I need to apply a filter to (the place is marked with a comment). I'm wondering if it's possible somehow to ...
0
votes
1answer
48 views

Using a subquery in a LIKE statement

I need to write a subquery inside a LIKE statement. This subquery would return me a word that I need to find in the middle of a sentence (that belongs to another field). Is it possible? This is how ...
0
votes
1answer
51 views

on every row on SELECT Query must lookup to other table

I'm working on Login/Logout System and I'm facing this problem regarding on SELECT Query. I have 2 tables namely dtr_tbl and shift_tbl. In dtr_tbl are the logs of the user(login/logout time details) ...
0
votes
1answer
32 views

Pick up the name of the column by two diff column in single table

Thank you guys in advance! i am desperately need to display my data in this way, My database structure is, Column idcat, parent_id and child_id is sharing single column name, catname. I tried ...
0
votes
1answer
201 views

MySQL Subquery Returns Null In Stored Procedure, Works Fine In Query

I have a subquery in a MySQL Stored Procedure where the value returns null even though the data is clearly there. Pulling the subquery out and running it works fine. I've checked the parameter values ...
0
votes
1answer
23 views

Querying All Records of All Users who have any instance of purchase behavior in their history

I am trying to write a query that shows me all user ids that in any instance of usage have a particular type of "purchase credit" associated with their account. I have one table, "users" that shows ...
0
votes
1answer
53 views

Stacked Left Joins or Left Join inside a Subquery?

OK, this is a fun one. Designing a database for a gaming system. I have 3 tables that are relevant for the query I'm trying to work on right now. character_skill_log with the relevant fields of id, ...
0
votes
1answer
109 views

Subquery on timestampdiff only allows for 1 result, how do I scale this for all results?

I'm currently writing up a query which is meant to output data to show curves of power profiles, and I'm facing a slight problem. So far, I've managed to do this: select datetime, PLANT_STATUS, ...
0
votes
1answer
72 views

How do I count and order by unique entries?

I have this: SELECT tblClient.[Printed Name], tblClient.[Address 1], tblClient.[Address 2], tblClient.City, tblClient.State, tblClient.Zip, tblClient.Phone, tblClient.[Email Address] FROM tblClient ...
0
votes
1answer
199 views

Need some advice creating this complex view

I need to create a view or query in MySQL to obtain if product has one or more product_detail. This are the create sentences for tables: CREATE TABLE IF NOT EXISTS `stock` ( `product` INT NOT NULL, ...
-1
votes
1answer
714 views

postgres: how to use subquery correctly?

I have the following query CREATE TABLE test_buffslip AS SELECT ST_INTERSECTS( (SELECT ST_Buffer(geom, 0.005) FROM segments where class = 7) ,geom) FROM segments; resulting in ...
3
votes
0answers
93 views

Why is my SQL Server query behaving differently on UPDATE than on SELECT?

I've written a SQL Server query that updates records to have a sequential number after partitioning on a field. When I run it as a SELECT statement, everything looks great: DECLARE @RunDetailID INT = ...
2
votes
0answers
33 views

Spatialite: use of virtual spatial index for distances

I'm currently learning to use spatial access methods for query optimization. I'm going through the examples in spatialite cookbook and got stuck in here ...
2
votes
0answers
1k views

Postgres Case statement not return else value in this query

I'm creating a SQL query with a some nested queries and I'm trying to use the CASE statement but it is behaving weirdly. I've been at it for at least 6 hours without any luck... This is my query at ...
1
vote
0answers
28 views

Update row value with a sub-select join

I am trying to update 1 column in all rows in a table to have an appropriate count value. Sadly, the SELECT query I am using won't let me join on a field in update table. The query looks like this ...
1
vote
0answers
31 views

Delete using join or using sub-query?

I need to delete rows from a table based on what is present in a temporary table. For me, both of these statements work : DELETE from main_table where `id` in (select `deletable_id` from ...
1
vote
0answers
99 views

Amazon Redshift: “*SELECT* 1” vs. volt_dt_0 vs. volt_tt_51343b6aa3bd4

I've noticed subqueries in Amazon Redshift can be represented in the explain plan in 3 separate ways: -> XN Subquery Scan "*SELECT* 1" -> XN Subquery Scan volt_dt_0 -> XN Seq Scan on ...
0
votes
0answers
53 views

Problem forming a query with subqueries in design view? (MS Access 2010)?

I just started a new job as a dbase admin intern, and due to the database being very large, it is better to do queries in design view, rather than hardcoding the sql. That being said, I cant seem to ...
0
votes
0answers
29 views

subquery in set clause retrieving different values

I want to update the column "kstplcode" using different values coming from column warehouse except the value "main" with their corresponding delivery numbers. I've tried this code but the set clause ...
0
votes
0answers
46 views

Smallest unique string among multiple columns

Somewhat similar to the Largest Common Substring (http://en.wikipedia.org/wiki/Longest_common_substring_problem) I'm trying to find the smallest unique string from a table. In some tables, its just ...
0
votes
0answers
52 views

Indexing on cached data from related table using triggers — Is there a better way?

Edit: Schema details per request from Aaron Betrand -- I'll break the schema down to the relevant fields of the two tables. Orders (ID int IDENTITY, Status char(1)) Services(OrderID FOREIGN KEY ...
0
votes
0answers
29 views

Subquery optimization

I was reading through this article - http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ I am working with an existing design. What I want is to be able to select ...
0
votes
0answers
69 views

Multiple subqueries performance issue

I have a query that collects customer data. I select a lot of correlated data from different tables. I want to have only ONE result row for each customer. The query looks like that: SELECT c.Id ...
0
votes
0answers
55 views

Sub queries in Apache Hive

I have a table with following structure col1 col2 col3 col4 category 300 200 100 20 1 200 100 30 300 2 400 100 100 70 1 100 30 200 100 ...
0
votes
0answers
3k views

Multiple SELECT subqueries in an INSERT statement in a stored procedure

So I had the following (genericized), which works just fine for me (barring errors in making the example generic) : DELIMITER // CREATE PROCEDURE stored_proc(IN val1 INT, IN date1 DATETIME, IN date2 ...
0
votes
0answers
168 views

Latest of group in subquery is bottleneck, how to optimizing query?

See Original question I'm fetching the activity data for the latest activity for each company as part of a bigger query. I've been using the method provided by ypercube, and it works fine, but I find ...
0
votes
0answers
170 views

Unable to send WHERE parameters to subquery

I have the following problem (in PostgreSQL in combination with PostGIS): I have the following nice query who clusters multiple records. Although it uses PostGIS function, my problem is not PostGIS ...
0
votes
0answers
45 views

subqueries with select

This below query works as expected: SELECT reports.* FROM reports INNER JOIN units ON units.id = reports.unit_id WHERE reports.unit_id IN (1111, 1112, 1113) ...
0
votes
0answers
235 views

Alternatives for subquery

There are three tables: product_hits, products, and product_subcategory_map. I want to count the number of product_hits and for each product. There could be more than one subcategory in the ...
0
votes
0answers
370 views

Alternative to TEMPORARY tables capable of re-opening in a query?

I use TEMPORARY table for calculation of temporary data, but TEMPORARY tables in mysql has a big limitation, which made it useless for me. A table cannot be re-opened in a query. This mean I cannot ...