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

0
votes
0answers
3 views

MSAccess: Error in correlated subquery

I receive the following error At most one record can be returned by this subquery. (Error 3354) A subquery of this kind cannot return more than one record. Revise the SELECT statement of the ...
0
votes
0answers
13 views

Access multiple queries

I have three queries: 1) SELECT [01BD_Cadastral_Activa].ID, Aux_Dates.Month FROM 01BD_Cadastral_Activa, Aux_Dates GROUP BY [01BD_Cadastral_Activa].ID, Aux_Datas.Month; 2) SELECT [03Agents|...
0
votes
0answers
33 views

Compare Previous and Current Year Sales

I am seeking advice on how to modify my sales query that will compare 2 separate years (current and previous years) and do some calculations for the two years. Below you will find my current sales ...
-1
votes
0answers
17 views

How to use count and groupby to create many columns [on hold]

I have a simple table named A with 3 columns: product_id first_date second_date. If first_date==second_date then up_to_date. if first_date<second_date then nearly_over_date, else over_date After ...
0
votes
1answer
111 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
31 views

SQL Server: Msg 512, Level 16, State 1, Subquery returned more than 1 value

This has me baffled, as I have tested multiple variations of the correlated subquery to no avail. Since the MAX function is suppose to guarantee a single result, I'm almost convinced that the problem ...
2
votes
2answers
206 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) Check-...
0
votes
1answer
148 views

Slow mysql query full text search tag database

In the database are ~700k images. Each image has its own tag(s). I want to be able to search images by the tags with a full text search query. The query does exactly what I want but it's very slow. ...
0
votes
0answers
30 views

Subquery or Inner Join?

I have this simple query that finds all transactions from accounts belonging to a specific login, with some extra conditions on the transactions. SELECT t.id, t.date, t.amount, t.description FROM ...
0
votes
1answer
191 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
0answers
42 views

Find Intermediate Bus Stop and Source and Destination Bus

I have a table which consists of 4 Columns: PKID, Bus_ID, Stop_Position and Stop_ID. Data: BUS_ROUTE_TABLE PKID BUS_ID STOP_POSITION STOP_ID ------------------------------- 1 1 1 ...
1
vote
2answers
123 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 ids....
1
vote
1answer
38 views

Using a subquery in place of a SELECT fieldname …or how to achieve the same result?

My goal is to get a collection of ID's from Table 1 based on a query, a collection of ID's from Table 2 based on a query, and insert a new record into Table 3 for each permutation. Basically, this: ...
3
votes
1answer
51 views

User variable in nested subquery not getting picked up - in complex sort of 2 tables with gaps

I have just recently learned about user variables (ie, @myvar) and am trying to create a complex sorting query that takes 2 tables where the second is missing a column which then gets filled in by a ...
0
votes
0answers
15 views

Postgresql - How to use NEW in subquery?

I have a table that I want to update. I wanna use the NEW in my subquery where clause but error says, Missing from clause entry for table "new". Additional Info details table - serial, ...
1
vote
1answer
12 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 ...
0
votes
2answers
91 views

Optimizing sql server MIN in the select blah where blah IN (SELECT MIN(blah)

is there any effective way to tune the query below? I have a filtered index on OrganisationID <> 0 Primary key is on another field, not relevant for this query ItemCode varchar(20). I have tagged ...
0
votes
1answer
23 views

Merge sub-queries to a single query

I am trying to merge the nested MYSQL queries into one First of all I fire this query select names , city , category from names_data group by names , city ,category ; this produces a result of ~...
0
votes
0answers
17 views

sum price of childs in other table mysql

i have two table one store data child and parent hierarchy and other paths and descendant +----------+------------+-----------+ | userid | parent | price | +----------+------------+--------...
0
votes
1answer
18 views

get minimum from result with group by mysql

i have table it store hierarchy data in mysql this table store stable relation but if each user les than 1000 buy removed and user User a lower level replace thia is my code and work fine, after qroup ...
31
votes
3answers
5k views

Why does this query work?

I have two tables, table_a (id, name) and table_b (id), let's say on Oracle 12c. Why does this query not return an exception? select * from table_a where name in (select name from table_b); From ...
1
vote
2answers
76 views

Help combining two MySQL queries to improve performance

I have two MySQL queries. The first one returns a list of codes, which comprise either one or two letters (e.g. "AB", "WO", "G"). This is the query: SELECT d.field_id_105 AS code, title FROM ...
3
votes
0answers
30 views

Tool to automatically refactor queries, replacing subqueries with CTEs [closed]

I have to mantain very big and complex SQL queries (more than 1000 lines) with many nested subqueries. These queries were made by other people and are very difficult to understand, so I want to ...
1
vote
1answer
34 views

Multiple subqueries in columns with joins to return multiple rows

I have a table which stores id(primary key) of users and not their names (Names are stored in another master table) and also the the department id of user and not the name of the departments and there ...
0
votes
1answer
76 views

A multi-table JOIN query as subquery to a query that JOINS the same tables. Good or bad?

I have the following tables in my postgres database - power_relations - (id, properties) - power_relation_members - (id, power_relation_id, member_id, member_type) - power_lines - (...
2
votes
3answers
78 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: ...
3
votes
1answer
62 views

Create index for multiple WHERE EXISTS subqueries

I have to find an efficient way to write a very specific query for an application that I'm developing. The idea is the following: Find all the genetic markers for which there exists at least one ...
1
vote
1answer
52 views

Optimize MySQL Query with multiple subqueries

I have the following Query: SELECT p.id AS post_id, p.status, p.user_id AS post_user_id, ( SELECT pl.id FROM postlocks AS pl WHERE pl.post_id = p....
0
votes
1answer
69 views

Searching a text with space in mySql match against Query

I want to search a text in mysql DB with [space] (Ascii 32) as a part of it. But I am not getting the correct result. My MySql Query is: SELECT count(id) as total , MIN(product_price) as ...
0
votes
1answer
37 views

Accessing computed column in a nested query

I am using nested sets to represent tree-type date in SQL-Server and basing what I'm doing on this article: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ by Mike Hillyer. In ...
2
votes
2answers
86 views

best way to avoid redundant aggregate functions and/or group by columns

Suppose I have two tables: Foo: id baz Bar: id foo_id boom So a Foo has many Bars. I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set ...
2
votes
3answers
67 views

Vendors with Most Invoices in State

I have the tables Vendors(VendorName, VendorID,...) and Invoices(InvoiceID,...) and I am trying to find vendors with the most invoices in their respective states. Please critique my work: SELECT Max(...
3
votes
3answers
49 views

Query to find missing status events

I'm hitting a road block as the best way to query a related table. I have two tables: main and status_history. Right now, I can identify records from main with no status_history at all, but I need to ...
1
vote
2answers
40 views

Does the EXIST clause only return distinct rows by its nature?

I have two queries that appear to be logically equivalent, yet they return different result sets. I am using the AdventureWorks2012 database. The first query uses an EXISTS clause with a sub query. ...
1
vote
1answer
29 views

MySql LIMIT + JOIN is stopping data from being returned?

I originally posted this question on stackoverflow, but then remembered there's a stackexchange specifically for databases! I've got two tables and am trying to return all profiles that are assigned ...
3
votes
1answer
71 views

Correlated subquery to find total larger than state average

I have tables Vendors (VendorName, VendorState,....) and Invoices (InvoiceID, InvoiceTotal,...). I want to get the Invoices (as InvoiceId) that are larger than the Avg state InvoiceTotal. I know I ...
4
votes
1answer
49 views

PostgreSQL join subquery can't restrict query

I'm working on a check-splitting project that splits out amount owed for an activity. Users can be a part of multiple 'groups', which each have their own running balances. I'm trying to run a query ...
0
votes
0answers
36 views

SQL Query with double distinct dates iteration, and start date < my date < final date

I have a kind of rental system database where user can rent an entire house, or just a room of the house. I have a table called offers which has columns id, room_id and a few more. If room_id = NULL,...
0
votes
1answer
32 views

ORA-00932 on subquery join from subquery join

The only guess I have is that perhaps the size of the subquery is considered a CLOB? I have no null values for the fields selected AFAIK. SELECT DISTINCT kbq_ent.qid AS kbq_ent_qid, kbq_ent....
0
votes
2answers
38 views

Error When Trying to Run Subquery

I am having issues getting my subquery to run properly. Everything runs quickly and correctly until I add the subquery. I get the error: "Msg 4145, Level 15, State 1, Line 66 An expression of non-...
1
vote
2answers
38 views

SQL Server — More efficient alternatives to repeating an (almost) identical subquery

I need to improve the efficiency of multiple subqueries within a WHERE clause. The context is a report generator on an existing product. The only input I can provide is the WHERE clause. I have 3 ...
4
votes
2answers
392 views

Outer self join filters vs subqueries

I have a table storing information about tickets. There is one record when ticket is sold and another when ticket is used. There is a column called TransType which is set to either 'sold' or 'used' ...
0
votes
4answers
126 views

How can I use MySQL variables in subqueries?

I'm trying to run the following query, but it keeps getting "reduced" and failing. Cannot get MySQL to provide any information as to why. Here's the query: SELECT * FROM animals WHERE name IN(SELECT ...
0
votes
1answer
65 views

Subquery returns more than 1 row

I'm executing a query to return the products of a specific sale in Magento database. It's working fine for a sale that contains only one product, however, I'm having Subquery returns more than 1 row ...
6
votes
5answers
37k views

Get multiple columns from a select subquery

SELECT *, p.name AS name, p.image, p.price, ( SELECT ps.price FROM product_special ps WHERE p.id = ps.id AND ps.date < NOW() ORDER BY ps....
12
votes
2answers
579 views

How can SQL Delete using a sub query

The following code was added by one of our developers to delete duplicate records from the table: DELETE SubQuery FROM ( SELECT ID ,FK1 ,FK2 ,...
0
votes
2answers
146 views

Are these two queries technically equal to each other?

Ran it in my query browser. I was am getting same results for both. But still need a confirmation. SELECT cs.RSCode , CustomerName , em.EmailId FROM Customer_STN cs JOIN ...
0
votes
0answers
49 views

Moving slow sub-query to With clause

I am trying to optimize this query : SELECT sum(a-alias.s_count) AS count FROM a-table a-alias WHERE ( a-alias.created_at_minute_id >= (floor(extract(epoch from( select ...
1
vote
1answer
58 views

Reuse subquery as in another result

In a typical social-network-like application I have the following query that gathers news from a set of followed channels. The main query gets the news, while the subquery gets the followed channels: ...
1
vote
2answers
102 views

how do I use a value from the select in a joined subquery?

I'm trying to avoid a stored procedure or bringing it into code but they may be my only alternatives. Here's my sql. Does anyone know any SQL voodoo that could make this happen without a stored ...