Questions tagged [subquery]

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.

Filter by
Sorted by
Tagged with
1
vote
1answer
397 views

Change DEPENDENT SUBQUERY to JOIN

I have the following query. It calculates the best match for each row in flight based on the the data in airport SELECT flight.id, ( SELECT id FROM airport ...
2
votes
1answer
3k views

Insert Into table ommitting columns that have default values

I have a PostgreSQL database with lots of tables of the same structure, 36 columns in total: CREATE TABLE some_schema.some_table ( id integer NOT NULL DEFAULT nextval('some_schema.id_seq'::...
-1
votes
1answer
23 views

SQL Subquery Help

I am little stumped on what to do with this query. The POs attached to them should only total as 1600 left open of that part number. However, instead it generates multiple rows unnecessarily. The ...
0
votes
1answer
92 views

How to get total cost per hotel and transfer per 8 days?

Problem How to get total cost per hotel and transfer per 8 days ? Details 8 days = 7 nights meaning 8 days = 7 days accommodation per hotel because last day he take flight not stay in hotel . ...
0
votes
1answer
939 views

MySQL update based on main table data with cross table subquery

I have the following SELECT statement which I have simplified here. It actually takes 0.6s to run as it does some calculations based on two tables (table1 & table2). SELECT table1.id,table1....
2
votes
1answer
29 views

selecting next todo item from child table

I've a table with millions of records, relationship is one (object) to many (object_items). object_items: CREATE TABLE `object_items` ( `item_name` varchar(50) NOT NULL DEFAULT '', `object_id` ...
1
vote
0answers
53 views

How to get total cost of start package table between two periods by sql query?

Problem: how to get total cost of start package table between two periods with a SQL query? alexaPackage from 28/06/2017 to 05/07/2017 8days cost ? Details I need to get total cost in start ...
3
votes
3answers
2k views

How to avoid a subquery in FILTER clause?

Schema: CREATE TABLE "applications" ( "id" SERIAL NOT NULL PRIMARY KEY, "country" VARCHAR(2) NOT NULL, "created" TIMESTAMP WITH TIME ...
0
votes
2answers
328 views

Optimization by replacing subquery with named subquery having INLINE hint

Let's have these two tables: create table table_x( x_id varchar2(100) primary key ); create table table_y( x_id varchar2(100) references table_x(x_id), stream varchar2(10), val_a number, ...
2
votes
1answer
65 views

Can data changes become visible between a subquery and an outer statement in PostgreSQL?

I have a PostgreSQL query that looks like this: DELETE FROM x WHERE x.id NOT IN (SELECT x_id FROM y WHERE ...); Is it possible for the DELETE to see rows that weren't previously committed and ...
1
vote
2answers
149 views

Possible to rewrite this query as a join?

I'm fairly certain it's possible to rewrite this as a join query but I'm just not sure how to approach it: SELECT events.*, (SELECT MAX(updated_at) FROM events e WHERE e.user_id =...
2
votes
2answers
1k views

Optimize a query with two range conditions

I have a structure similar to this one: CREATE TABLE `author` ( `id` int(11) unsigned NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB CHARSET=utf8 ...
1
vote
1answer
382 views

Do too many subquries create multiple sleeping processes in MySQL?

I am using java & MySQL to make an app. When I check the process list. I see a lot of sleeping processes. It also reaches to a points when no further connection is allowed. I have read many ...
3
votes
2answers
251 views

Join is causing other column data to change?

I'll lead off with saying that I am relatively new to databases and SQL and I am trying to make an addition to a View in SQL Server that a previous co-worker created. I am trying to join the ...
2
votes
2answers
284 views

Count bookings and cancellations per member (select in subquery)

That will be my very first query using subquery ever but I already have problems. Let's take a look at my tables: user - id - name user_visit - id - user_id - visit_type_id (BOOKED, ...
1
vote
1answer
5k views

Filtering subquery results with group by

I have a join that works but seems hacky because it orders the subquery and uses group by to filter everything but the top subquery result. Without doing so, the cartesian product of s and ...
0
votes
1answer
206 views

Select one row from n number table; Join / Union table with looping

I have a list table. locations_list id | location | table ---------------------- 1 | Location 1 | location_1 2 | Location 2 | location_2 3 | Location 3 | location_3 location_1 ...
1
vote
0answers
79 views

How to populate a 'map' table from words of a text?

Having the database structure that follows: CREATE TABLE articles ( article_id int(11) unsigned NOT NULL AUTO_INCREMENT, article text, PRIMARY KEY(article_id) ) ENGINE=InnoDB; CREATE TABLE words ( ...
4
votes
2answers
1k views

Cross server query using SSIS

I need to issue a query against DBs on two servers: [CRM].[CRM].[dbo].[BasicInfo] (NationalID PK, CustomerCardNo) [ERP].[ERP].[dbo].[Order] (OrderID PK, CustomerCardNo) And what I want to do is like ...
0
votes
1answer
1k views

Removing duplicate results via subqueries in reference to join conditions for multiple tables

With the code below I was originally attempting to not use sub queries to eliminate "duplicate data" (its technically not duplicate data since each order has several lines of data where prices and ...
6
votes
2answers
350 views

PostgreSQL randomising combinations with LATERAL

In the following example I have a table foo from which I'd like to pick out at random a row per group. CREATE TABLE foo ( line INT ); INSERT INTO foo (line) SELECT generate_series(0, 999, 1); Let'...
15
votes
1answer
367 views

Poor performing subquery with date comparisons

When using a subquery to find the total count of all prior records with a matching field, the performance is terrible on a table with as little as 50k records. Without the subquery, the query executes ...
0
votes
1answer
1k views

Joining subqueries with GROUP is slow

Can anyone help me with the following query? SELECT voc.username AS Medewerker, voc.bestvastonbep_count AS Doorgezet, IFNULL(cc.call_count, 0) AS Telefoongesprekken, voc.bestvastonbep_count / ...
0
votes
3answers
150 views

Rewriting query with subquery to improve performance [closed]

My query looks like that SELECT topics.name, topics.topic_id, topics.creator, ( SELECT COUNT(*) FROM answers WHERE answers.topic_id = topics.topic_id) AS replies FROM topics ORDER BY topics....
2
votes
2answers
5k views

Complex joins in multiple subqueries

Sorry to keep bugging you guys, but SQL is still new to me. I have these tables: Store_location, Product, Sizes, Sells, Available_in, Offers, and Currency. The goal is to be able to run one query with ...
1
vote
0answers
359 views

Pass argument to deeply nested query

This overly complex query seems to work when I use a hardcoded value for gid=node.nid (eg. gid=123), but when I use it like below, I get the error following: Unknown column 'node.nid' in 'where ...
3
votes
2answers
16k views

MySQL LIMIT within JOIN

I have an 1:m association. symbols table has many rows in company_key_statistics table. company_key_statistics has column createdAt which is timestamp indicating when row has been created. I need to ...
0
votes
1answer
213 views

Can't optimize a nested query using indexes

There are two tables containing hundreds of thousands of records: client and task. It's necessary to query the following: SELECT id, (SELECT status FROM task t WHERE t.client_id = c.id AND ...
2
votes
1answer
2k views

Should i use subquery to limit table before a join?

In the case of a join followed by a where clause, would it be better to use a subquery to limit the results, and then do the join? Example: SELECT * FROM Customers NATURAL JOIN Orders WHERE ...
4
votes
2answers
28k views

MYSQL Subquery in columns of select statement

How are subqueries in the column field of select (projection) paired with the result of the main query? in the form: SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id) as name FROM ...
2
votes
1answer
287 views

Why does query runtime dramatically increase when made into subquery?

I'm working on a query that selects counts from 2 servers and joins those together. Here is what I have: SELECT a.NAME , SUM(ISNULL(b.ttl,0)) AS [Total] FROM TableFunction(@user, DEFAULT) a -...
0
votes
2answers
738 views

Full outer join in MySql

I'm on MySQL trying to get the user's daily post count grouped by post category. So for every day in a given date range, with no gap, I need the post count for each existing post category for a given ...
3
votes
3answers
3k views

Performance of count(*) in subquery

Suppose we have the following queries: 1. SELECT COUNT(*) FROM some_big_table WHERE some_col = 'some_val' 2. SELECT COUNT(*) FROM ( SELECT * FROM some_big_table WHERE some_col = 'some_val' ...
0
votes
1answer
238 views

Performing join on 3 tables & retrieving selected columns

I need to write a query which fetches data from 3 tables & retrieve selected columns out of it. The query below works fine. I am interested to include 1 more column in my SELECT statement which is ...
1
vote
2answers
171 views

Can some redundancy in this nested SELECT COUNT query be eliminated?

For the record, this is not a homework question. I am a professional brushing up on my SQL skills. Given a table called world that looks like this, I want to find the continents where all countries ...
1
vote
1answer
112 views

Finding posts with exactly set of tags per user

I'm trying to determine the best way to find all posts that have an exact match with a list of user tags. For example, user has tag IDs: 1,5,9, and 11, while post has tag IDs 1, 5 and 9, thus this is ...
3
votes
2answers
12k views

Error converting NVARCHAR to BIGINT when in SUBQUERY with WHERE clause after invalid records filtered [duplicate]

I have a table that has an NVARCHAR column that contains data that cannot be converted to BIGINT. I'm well aware of that and have filtered it out using ISNUMERIC(BB.NVARCHARCOL) = 1. Despite this, I ...
0
votes
1answer
48 views

Insert Into with Select works in once instance, but not the other. Subquery error or incorrect data

I am trying to write a script that will combine specific survey results based on the demographs used in the survey. I have been able to write a script that adds demographs together to create a new ...
0
votes
1answer
58 views

select subqueries with join

I am using sub queries using joins. For 11946 rows, the query is taking 18 seconds. Select CONVERT(VARCHAR(10), [Date], 101) as schedDate, ClientName, Coalesce(Error1, '') + CASE WHEN Error1 is ...
3
votes
2answers
5k views

Why does COALESCE function not work for this query?

I am trying to count the number of columns that is retrieved from the subquery, and some subqueries may be empty. So instead of displaying it as an empty cell, I want it to be 0. Found out that I can ...
-1
votes
2answers
1k views

Subquery returns more than one row, any doesn't help

select first_name, middle_name, last_name from students where student_id = any (select student_id from entries where subject_id = (select subject_id ...
2
votes
2answers
709 views

Slow CREATE table from subquery using SELECT .. INNER JOIN

This query takes ~90 seconds: CREATE TABLE filtered ( SELECT DISTINCT f2.cityCode, f2.productCode, f2.productDesc, f2.inventoryDate, f2.inventoryLevel FROM etm ...
1
vote
1answer
647 views

Correlated Subqueries: resolving ambiguous names

Suppose I have 2 tables which share some column names. In this case the primary key is both is called id. CREATE TABLE artists( id int primary key, name text, -- …, ); CREATE TABLE ...
1
vote
1answer
85 views

SQL multiple select with where conditions performs really poorly

Data structure is something like this: Number| ID | DateTime | Status 50000001, 101, 1/1/09 0:10, PO 50000001, 110, 1/1/09 0:11, PO 50000001, 102, 1/1/09 0:15, PO 50000001, 101, 1/1/09 0:10, PCK ...
4
votes
1answer
14k views

Passing a value to a nested subquery

I have the following query (stripped down for brevity), the aim of which is to create the w8 value used to order the results: SELECT elements.id, [ ... ], (SELECT COALESCE(craft_w8_a.weight, 0) +...
2
votes
2answers
472 views

Overcome SubQuery Returned More Than 1 Result

I found this syntax already answered to a diff question, but I am getting the error of More than 1 result returned what do I need to alter in my syntax in order to defeat this error? Declare @ONE ...
0
votes
1answer
252 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, ...
1
vote
2answers
219 views

PostgreSQL: windowing to replace multiple sub-queries … hopefully

I have two tables: CREATE TABLE fund_data ( id serial NOT NULL, fund_entries_id integer NOT NULL, fund_val numeric(25,6) NOT NULL, bbg_pulls_id integer NOT NULL, CONSTRAINT fund_data_pkey ...
0
votes
2answers
267 views

How to query with the condition with subquery [closed]

I have two tables table A and table B. table B has a field called condition (where some sql conditions are stored). Now i wanted to query like this select * from table_A as a where a.id = 1 ...
2
votes
1answer
9k views

Correlated sub-query on the same table with only one alias

I have query which look like this SELECT * FROM my_table WHERE ... AND id IN (SELECT t2.id FROM my_table t2 WHERE id = t2.id AND ... ); I know in this ...