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
1answer
20 views

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

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
44 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
95 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
68 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
37 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
51 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
25 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
17 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
26 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
12 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
121 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
27 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
48 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
33 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
17 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
43 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
142 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
359 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
39 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
12 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
175 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
62 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
43 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) ); ...
2
votes
0answers
21 views

How to fetch related rows on multiple condition with ranking

I have following table for storing item information in PostgreSQL. id | name | category | seller | m_age | status | type_id | region | ----------------------------------------------------------...
5
votes
1answer
285 views

Include non-inserted rows as result of INSERT INTO … RETURNING

I'm using PostgreSQL 11. I want to conditionally insert values into a table while having the result of the insertion include a null for each row of the input which did not result in an insertion. For ...
0
votes
1answer
22 views

Is there a neat way to build a user posts/likes/comments query?

Say we have table users and three tables posts, comments, likes, which are many-to-one to users. I want to build some report query for all users, even if they have 0 posts, comments or likes. Is there ...
0
votes
1answer
43 views

MariaDB, SEQUENCE engine, dynamic upper bound

I'd like to generate a sequence in MariaDB starting at 1 and ending with whatever is the largest id in some other table. That other table has roughly this schema: create table main ( id integer ...
0
votes
1answer
34 views

Reusing sql query values for another query in single request

I have an sql table for some chats with a table messages, containing id int pk, chat_id int, created_at timestamp. Given a message id, is it possible to select it, along with 5 messages after it ...
0
votes
0answers
26 views

I'm having trouble generating a list of downloadable items (data from three tables) with a column showing most recent download time for each

I am using the following query to generate a comprehensive download log with data from multiple tables (reduced to three for this example): SELECT wp_download_log.ID ,wp_download_log....
2
votes
1answer
623 views

Sql Server string concat with order by and subquery

While working on some dynamic sql production code we encountered code that's concatenating a bunch of strings and a subquery and then more string concatenation. The goal of the code is to populate a ...
-1
votes
1answer
25 views

Comparing the result of a subquery with LIKE

I need to find all the movies that have all of its actors' names to start with an A and have a 60.000.000 or more of gross. I have this but is not working SELECT title, imdb_score FROM Movie AS m ...
0
votes
1answer
59 views

Select rows from table having more than two specific values

I have a table having details such that Userpin product Time1 contains 4556 23 7:30 A 4557 23 7:30 B 4558 23 7:30 C ...
0
votes
2answers
37 views

Inherit INNER JOIN fields in MySql SELECT QUERY

I want to know how I can join one field of an INNER JOIN table in a SELECT QUERY. Here's what i got so far: In this query my result would be NameA, NameB and idC, but i want to join tableA and tableC ...
0
votes
1answer
19 views

Select rows from table having specific values depending on another values in the same table

I need to be able to select all Userpin that have tr_type 7 only but if there is tr_type 7 and 8 in other row ignore 7 and 8 example: Userpin TrDate Time1 Tr_type 4555 12-02-2020 ...
0
votes
2answers
38 views

Using subquery within IF condition [closed]

Hello i am having a weird problem currently i have the following query SELECT * FROM `txt_campaign_message` `cm` LEFT JOIN `txt_campaign` `c` ON c.id = cm.campaign_id LEFT JOIN `...
0
votes
1answer
209 views

Will subquery (using IN) run multiple times for update statement (SQL Server 2017)

Where an update statement is updating the value returned by a subquery - will the subquery be run for each row? UPDATE a SET DM = 1 FROM TableA a WHERE a.ID NOT IN (SELECT DISTINCT ID FROM TableA ...
0
votes
0answers
55 views

Multiple sub query in insert statement for postgreSQL

I am trying to insert into table with multiple queries. And its not working, here is my work. insert into post_topics_ta (id,post_id,topic_id,confidence_score) SELECT * FROM post_topics WHERE ...
0
votes
0answers
36 views

Improve MySQL query efficiency for first row in a group

I've written the query below in MySQL to get the top 10 top landing pages across all browser sessions. Reading other similar posts about how to access the first row in a group, it seemed like the ...
0
votes
4answers
56 views

How to visualize joins between inner queries to their respective outer queries?

I seem to always have trouble visualizing what the meaning is when an inner query is joined to it's outer query. Here's an example: SELECT NM.MovieId, NM.MovieTitle FROM NinjaMovies AS NM WHERE ...
0
votes
2answers
78 views

MySql LOAD DATA with subquery performance issue

I'm migrating data into a MySql (5.7.26) database (32GB ram), running as a managed service on AWS. While importing the data, I need to map one of the columns of the CSV being imported to another value ...
0
votes
1answer
39 views

How to show multiple record if has top 1

Hi currently having difficulty to query the result sorry a newbie here. i am trying to find the result of these two tables. i want to get all the records on table 2 with an effective date < 2019-12-...

1
2 3 4 5
12