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
0
votes
0answers
20 views

Change from MYSQL variables to a subquery

The following below works, but I am trying to get away from the first table and using variables and putting creating a subquery instead. Thoughts? Ideas on getting it working? CREATE TEMPORARY TABLE ...
0
votes
1answer
49 views

SQL (Postgres): Subquery in WHERE clause with IN operator is slow

I have the following SQL Statement: select count(*) from person p cross join task t where t.id in ( select tp.task_id from task_participant tp where tp.person_id = p.id ) and p.id = 1234 ...
1
vote
1answer
28 views

Getting the “flat” query corresponding to a view using nested views?

I'm programming an application that queries SAP Business One databases, which are incredibly complex, riddled with cryptic table and column names left over from ancient times (including many ...
0
votes
0answers
16 views

Getting a list of records that don't have any tags associated with them

Two tables: CREATE TABLE `records` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` longtext COLLATE utf8mb4_unicode_ci, `tagged` tinyint(1) unsigned NOT NULL, PRIMARY KEY (`id`) ) ...
0
votes
2answers
39 views

How to retrieve missing values of an id from a list?

have a data structure that is value/tag inside a table on Oracle sql Lets say I have this structore exTable ID TAG VALUE 1 1 x 1 4 y 2 2 x 2 3 y 2 4 z I want to retrieve the missing tags for a ...
1
vote
2answers
60 views

Mssql subquery inside a substring

I need to remove the last character of my subquery but I cannot figure out how to reference my subquery in the subselect as I need the length of it. The subselect collects all rows and converts them ...
0
votes
1answer
38 views

Unexpected unique constraint violation during update

In table foo with two columns (id and seq), I'd like to add +1 to seq for all records with an arbitrary seq > 4738. The plan is to insert a new record at seq=4739 once all the seq > 4738 records ...
-1
votes
3answers
42 views

I am trying to find the average of a COUNT request [closed]

I have completed the query below from an invoices table which gives me the total invoices attached to each house ID. So house_id 114 has 14 invoices attached to it. Is there a way to feed this result ...
0
votes
3answers
27 views

Query to fine “Order_num” that has no two equal “product_name”

I have a table with below structure: create table A_TEST ( order_num int, product_name varchar(50), product_amount int, product_price int ); Sample data : insert into a_test (...
6
votes
1answer
123 views

Why is SQL Server spliting it's (JSON) response into multiple rows?

I am trying to build a query that results in a JSON object generated by SQL Server. I've found that I can use subqueries to populate a field (in this case the questions field) with a JSON string that ...
-1
votes
1answer
41 views

Write a dynamic query of nested subqueries [duplicate]

Let's take an everyday query with a list of nested queries so I can show you the problem, see how we'd have to add a subquery when a new Certification comes out: select DISTINCT e.display_name as &...
0
votes
1answer
40 views

Sql Group by on subquery rows

I have this table : ArretProductionJournee(Id, DateArret,HeureDebut,HeureFin,EnumArret) Example : DateArret ||HeureDebut ||HeureFin ||EnumArret 2020-11-30 ||2020-11-...
0
votes
1answer
39 views

How to delete Specific row

http://sqlfiddle.com/#!9/df6245 How to delete all rows Where brand not equal to 107 for example in above fiddle GRPCD 44111 have 107 so it should not delete both rows which have GRPCD 44111 but it ...
2
votes
2answers
27 views

How to filters data with two windows conditions?

I want to get the result of two sub-queries. In my case I have users, user can have multiple access for doors, but sometimes doors are disabled so the access exist but do not point to any doors. I ...
-4
votes
1answer
38 views

Error when running query as a subquery [closed]

The inner query runs successfully alone, but when I run it as a subquery, an error pop up. My query: Select TOP 3 * FROM (select fr.rating , sp.SpId ,sp.FName ,sp....
0
votes
2answers
38 views

Optimize duplicated but negated subquery

I have what I call a 'Hamlet' query in the form: select * from stuff a left join otherStuff b on (a.x=b.x) -- lots of joins ommited where (b.ToBe = 1 and a.Being in (select Being from stuffThatIs ...
2
votes
1answer
19 views

SQL daily value (today value - yesterday value)

I have a database where there are sales . +----+-----------+------------+ | ID | Quantity | DATE | +----+-----------+------------+ | 1 | 40 | 2020/10/10 | | 2 | 0 | 2020/10/01 |...
0
votes
1answer
25 views

Using alias in Group by

I am running into a problem where I have to use an alias (Ordernum) in a group by. I have found that this could be done somehow with subquery but I don't have any success with that and also those are ...
0
votes
2answers
27 views

Independent aggregation of related entities, while also sorting by related (in a single statement)

I have a model with books and authors, with a many-to-many relationship between them (because a book can have multiple authors, and an author can write multiple books) through a table which I've named ...
0
votes
1answer
67 views

Why is this postgres query ignoring the index in the subquery?

I have a couple of tables, companies and datapoints. If I query for datapoints from a given company, it uses the index: sophia=> explain analyze select count(distinct(source_doc)) from public....
2
votes
3answers
149 views

MySQL reuse select aliases

I currently have a query where I'm doing two subqueries to get X, Y data: SELECT t.series AS week, ( ... ) X, ( ..., AND ... ) Y, ROUND(( ... ) * 100) / ( ..., AND ... ), 2) Z FROM series_tmp ...
3
votes
1answer
70 views

Query optimisation

I want to optimize 2 queries from 2 tables(orders, actions) with relation one to many Now I make one select from orders: SELECT "id" FROM "orders" WHERE "orders"."...
0
votes
0answers
11 views

pulling most recent results for each point based on date field

so I have tried a few methods now mostly using max(date) and not really understanding row (in) and aggregate. I have two tables in which I am doing a join that requires some field formatting to ...
1
vote
0answers
65 views

Correlated subquery with common table expressions in SQLite

Yesterday I achieved a 500x performance boost after rewriting an EXISTS with IN. I did some investigation to find out why and finally came to the following example. Basically, we draw 5 lucky numbers ...
-1
votes
1answer
59 views

MYSQL Error: Incorrect usage of UNION and ORDER BY?

Am trying to randomize my first query but ran into an error "Incorrect usage of UNION and ORDER BY" Kindly find below my queries: SELECT * FROM( SELECT deal_page.id, ...
0
votes
0answers
28 views

insert or update with summary subquery

i have a problem with follwing query INSERT IGNORE INTO cartellino_anno (iddipendente, mese, ordinarie,effettive,assenze,straordinari,ritardo,anticipo,richiestaassenza,richiestastraordinario,...
0
votes
1answer
18 views

MySQL subquery with WHERE filters not working as expected

I have a question/tagging system like StackExchange. I want to display the tags associated with each question and also show the total number of times the tag is used, unless the question has been ...
1
vote
2answers
27 views

MySQL - GROUP BY not working with multiple CASE WHEN statements

I am working on a database with Kickstarter database, and I'm trying to: Define short, medium-lengthed, and long campaigns See how much each length of campaign raise Convert different currencies into ...
1
vote
0answers
15 views

subquery returns more tha one row

I want to update data of a table where data-name matches with another table and also one data are common with many items. For example, I have a cycle in which tyre is a part and quantity of tyre is ...
0
votes
1answer
27 views

how to replace with default value when querying a JSONB column with a key that is not present in a particular row giving empty result

I have a table that has a jsonb column as 'result' which has an array of objects. Looks like this for every row: [{"grade": "A", "subject": "MATH"}, {"...
0
votes
1answer
13 views

Return average using same table data

I've got this query I'm testing with: select sum(units) / (select count(distinct week) from record_sales_data where customer_country_id=2 group by customer_country_id) as average from ...
0
votes
1answer
15 views

Query finds no values when sharppend

I am trying to join two datasets and find the most recent certification date I the following 2 datasets Certifications (1, 'Judge Level 1', 'Judge', 10, 2), (2, 'Judge Level 2', 'Judge', 20, 2), (3, '...
5
votes
2answers
177 views

Does Postgres optimize this JOIN with subquery?

In Postgres 12, I have a table purchase_orders and one for its items. I'm running a query that returns PO's for a given shop and a sum of items ordered on each PO: SELECT po.id, SUM(...
1
vote
2answers
31 views

how to sum values from subquerys

i need to collect some values from multiple tables and sum this values as a column to the result table. my query looks like this: SELECT u.ID as id, ( SELECT `meta_value` as user_name FROM `...
1
vote
0answers
14 views

Why did Codd, et al. prohibit intermediate scalars in SELECT - and how would they have recommended avoiding syntactic repetition?

(I'm asking this because I was reviewing this older question I asked and because I'm currently writing some queries and getting annoyed at how much I need to repeat myself). In ISO-compliant SQL ...
0
votes
2answers
49 views

T-SQL: how to show the corresponding column's row to an aggregating function's result?

Everyone, I have a question - how to show the (possibly ambiguous) column that corresponds to the value retrieved by an aggregating function? Related to the dataset below, the task is to find the ...
0
votes
2answers
53 views

Selecting distinct rows from two tables, using multiple conditions

I'm trying to figure out a query. I have an offices and meetings table in my DB. Offices can have multiple meetings (office ID is a FK to the offices table). Some meetings allow lunch, some do not. ...
1
vote
1answer
18 views

How to JOIN a table with itself while using a sub-query?

I JOIN a table to itself as SELECT t1.TagID AS TagID1 ,t2.TagID AS TagID2 ,COUNT(1) FROM TagMap AS t1 JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID GROUP BY ...
1
vote
1answer
45 views

T-SQL query - how to AVG() the SUM() of columns' rows to a new row column?

What is asked is to find the average marks for each student comprised by the total of the marks divided by their count. How should the query look like to achieve this, since the AVG function is an ...
0
votes
1answer
20 views

How to split a SUM column based on another BIT column? [duplicate]

MySQL v5.7 Having this table: id | sprId | load | calc -----|----------|---------|--------- 1 | 1 | 1 | 7 2 | 2 | 1 | 1 3 | 2 | 1 | ...
0
votes
1answer
440 views

Subqueries for JSON path where outer query is part of a UNION result in strings rather than JSON

Bear with me - the example code has bit going on and I'll do my best to explain it, below. with ENTITIES as ( select [name] as entityName from (values ('A'), ('B'), ('C')) X([name]) ), PROPERTIES as ( ...
0
votes
1answer
1k views

ERROR: subquery must return only one column

I have two tables one is Category and other one is Product. Table Description are Category name description parent_id (self referencing key) (can be upto 3 levels) Product name description type ...
0
votes
1answer
142 views

Converting rows to columns using crosstab in PostgreSQL not working (relation “table” does not exist)

I need to use the compiled data using CTE and then convert the columns to rows using crosstab(open to other ideas) in the next select statement. Below is the query. with checked_adgroup AS ( ...
0
votes
0answers
13 views

Debugging performance differences (and issues) between a query with a double-subquery, a single-subquery and an all inner-join statements

I have a complex business logic that requires me to perform a 2-levels nested query. The queries are generated by Django's ORM. At the bottom of the question I'll provide the queries as-is as well as ...
0
votes
1answer
28 views

Optimize query with a subquery (index is not used)

I have following query: SELECT `status`, `is_client_coordinator`, id, first_name, last_name FROM `users` `t` WHERE t.status!="CLOSED" and t.is_client_coordinator or t.id in (select ...
1
vote
1answer
505 views

How to select subset JSON with PostgresQL?

I'm trying to deal with a JSON column that does not have an explicit JSON schema or convention. There are varying keys within the JSON. Some keys do not exist, some keys do exist but empty string ...
0
votes
0answers
7 views

Count returning customers with several conditions

I have PostgreSQL 9.6.10 and a table like this one: CREATE TABLE web.orders (id integer NOT NULL, user_id integer NOT NULL, status_id integer, created_at integer); INSERT INTO web.orders(id, ...
0
votes
1answer
96 views

PostgreSQL error: “subquery uses ungrouped column”

I have a query with subqueries that use only elements from the GROUP BY clause: SELECT DATE(cm.created_at) as "date", COUNT(1) as total_message, room_id, (SELECT ...
0
votes
0answers
16 views

Comparing sum against the average of all sums

I am looking for a way to compare the payroll of a team compared to the average of other teams in the same league. Here is the table structure, three standard tables and two connecting tables to ...
1
vote
2answers
60 views

Getting a query to work on SQLite as well as on MySQL

I have a subquery with a condition in the main query where clause. Here is the sample schema CREATE TABLE `payments` ( `id` int(11) NOT NULL, `discount_id` int(11), `amount` int(11) ); ...

1
2 3 4 5
12