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.
716 questions
1
vote
1
answer
195
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 ...
- 250
1
vote
1
answer
1k
views
Can we use subquery in GRANT statement in postgres
I want to grant select privilege on few selected tables.
Can I use sub-query in GRANT statement????
I have tried the following query:
GRANT SELECT ON TABLE
(SELECT array_to_string( array_agg(...
- 13
1
vote
1
answer
67
views
Return the lowest value
How do I go about getting the course number (CNO) of all the lowest enrolled by department?
For example if math had coursenum 123 and the 0 enrolled, and anoher coursenum 555 with 6 enrolled, it ...
- 111
1
vote
1
answer
2k
views
Is it more efficient to union from sub-query or combine two outer queries?
I searched for this but couldn't find the response I needed. So my apologies if this has already been asked.
I'm asking in regards to DB2 but I suppose this would apply, generally speaking, to just ...
- 249
1
vote
1
answer
94
views
Why conditioning before and after join, gets different results?
I want to left join two tables and then return some of the records.
My first query was:
SELECT *
FROM card
LEFT OUTER JOIN user_card ON card.id = user_card.card_id
WHERE card.package_id =14
AND ( ...
- 13
1
vote
1
answer
7k
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 I'...
- 11
1
vote
1
answer
75
views
Slow execution of a subquery with 0 matches
In MySQL, I have two basic tables - Posts and Followers:
CREATE TABLE Posts (
id int(11) NOT NULL AUTO_INCREMENT,
posted int(11) NOT NULL,
body varchar(512) NOT NULL,
authorId int(11) NOT ...
- 111
1
vote
1
answer
2k
views
Oracle: summing column value with subquery?
Are there some restrictions in Oracle that disallows to have a WHERE clause that compares value of column+(subquery count(*))? If the subquery table is empty, count(*) doesn't yield to zero in this ...
- 13
1
vote
2
answers
1k
views
Subquery or multiple left joins, or both?
I have two tables, for simplicity table1, with id as Primary key, and a field name, and table2 with the same. Each table has quite a few records, but during a complicated form process, the user is ...
- 13
1
vote
2
answers
1k
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 ...
- 745
1
vote
1
answer
101
views
Counting number of visitors who did NOT log in
I'm trying to calculate the number of visitors to the homepage of my site who did NOT then log in to the member's section of the site, while also filtering bots and admin users. Bots and admins are ...
- 113
1
vote
2
answers
311
views
Using max for each sub group does not seem to work.
I have a table that looks like this in Oracle 11g:
+----------------------------------------------------------+
| ACCT_NBR | MAIL_TY | ORGA | PERS | RUN_DATE |
+--------------------------...
- 21
1
vote
1
answer
497
views
How to re-arrange rows to best fit within groups?
I categorized a table with the method introduced here to categorize my items to folders with a limit size of 100.
mixing_order id num_items cat order_in_cat cumulative_sum folder
1 1 ...
- 4,531
1
vote
1
answer
456
views
Getting max values from MySQL tables
I have three tables:
competitions (id, name, date)
athletes (id,name)
results (place, id_athlete, id_competition, ranking_points)
where:
results.id_athlet=athlet.id
results.id_competition=...
- 11
1
vote
1
answer
20
views
In MS Studio how to SELECT from an existing SQL file
I have a complicated query that select parts from an MRP table using several different criteria. The output of the query is a list of parts which includes the key field [PartNum]. I have saved this ...
- 11
1
vote
0
answers
68
views
Inconsistent result when running same query on slightly distinct MariaDB versions
I'm investigating the difference in the results given by the same SQL query on two servers running slightly different versions of MariaDB (10.5 and 10.11).
Test query is:
SELECT a.Name
FROM Artist a ...
- 111
1
vote
0
answers
19
views
postgresql sub query
I have three tables
kit - contains kit_id and kit_type (kit type is with android or IOS)
appointment - contains appointment_id, created_datetime, kid_id, created_by_id
user - contains user_id, user ...
- 11
1
vote
1
answer
477
views
How to return list of content from subquery for the main query?
I have a create that have sub query. To show as example, here is a query that has similarity with my own. Personally I have more table and more arguments on table (The subquery have left join for ...
- 181
1
vote
0
answers
25
views
query customer retention over range
I am trying to find the best way to accomplish the following.
Get the beginning customer count, which carries from the previous day
Get New Customer count
Get the number of Customers who have not ...
- 111
1
vote
1
answer
499
views
Parallel index-only scan with two sub-queries is slow
I'm trying to analyze a slow query that uses parallel index-only scan of two filters (using sub-queries). Specifically I'm the number of 'open_questions' that returned by either subplan1 OR subplan2.
...
- 135
1
vote
1
answer
162
views
DIV and GROUP by issues
I have one query, made it in MySQL.
And now try to run on PostgreSQL, but get syntax error twice.
Once on the DIV command, and then on the GROUP BY.
Here is my code:
UPDATE status_table
SET Status_CG ...
1
vote
0
answers
193
views
Return back only top N rows for each ID - SQL
I have written this SQL after browsing different topics. The topic I got the solution from -> Limit Records
MySQL version: 5.6
SELECT op.*
FROM (select op.*,
(@rn := if(@s = order_id, @...
- 11
1
vote
0
answers
458
views
Postgres: one-to-one join with duplicates and inequality matching
** Update: Fiddles now included https://dbfiddle.uk/?rdbms=postgres_14&fiddle=0c618e64c7fcd54c8781ed7d1ac540f0 and https://sqlfiddle.com/#!17/12053 **
I have two tables taken from two data sources,...
- 11
1
vote
1
answer
502
views
Inconsistent results with MySQL, query never ends on MariaDB
We have some really strange behaviour on MySQL (8.0.29) that we can't explain.
We have 2 tables + 1 link table in-between, illustrated by this schema :
We run a query whereby we need to fetch the id ...
- 107
1
vote
1
answer
2k
views
How do I get the sum of a field and avoid duplication from an inner join?
I am pulling Geo from the Accounts table and I am ranking the Geos by Opportunity $$ Amt based on Accounts that have the name like "Brinks%".
Accounts are unique, and there can be many ...
- 13
1
vote
1
answer
93
views
Is there anything I can do to optimise this complex query?
I have a rather complex query that spans several layers of subqueries, the two primary ones fetching data from the exact same fields, only one takes the values themselves, the other takes aggregates.
...
- 151
1
vote
0
answers
132
views
Ranking and use of cube by in an SQL query? [closed]
I am a the MSc student who does not understand this following SQL dilemma and want to be certain from an Academic standpoint.
The entire “Foodmart” database can be downloaded from: http://didawiki.cli....
- 11
1
vote
1
answer
103
views
Query to find the "Number of days" a customer has an active card between a specified interval
I have a table with below structure :
create table test_table
(
customer_num NUMBER,
card_number char(2),
card_issue_date DATE,
card_expire_date DATE
)
Example data is:
...
- 1,486
1
vote
2
answers
2k
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 ...
1
vote
2
answers
537
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 ...
- 11
1
vote
0
answers
37
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 ...
- 11
1
vote
0
answers
20
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 ...
- 603
1
vote
1
answer
3k
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 (
...
1
vote
1
answer
4k
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 ...
1
vote
2
answers
3k
views
MySQL unknown column in subquery
I'm running a fantasy game where the ratings for each player are calculated in the following way:
Each round (gameweek) a player plays zero, one or more games. The rating for that round is the ...
- 113
1
vote
0
answers
43
views
How to create subquery on multiple result columns?
I have two tables, both sharing the following columns:
table inventory: id, key, timestamp
table stocks: id, key, timestamp
I want to select all occurrences of the key from inventory table together ...
- 321
1
vote
1
answer
27
views
Join or Replace colum value based on query
I have this query:
SELECT h.name, i.name, i.delay, i.delay_flex, i.status, i.templateid, i.params, i.description
FROM items as i
LEFT JOIN hosts as h
ON i.hostid = h.hostid
AND i.type = 11
AND i....
- 25
1
vote
1
answer
3k
views
Display subquery columns in result
I'm new to SQL (also my first question) and have been trying to determine the best practises and most efficient way to code my problem. I'm using mySQLWorkbench on MacOS. I'm trying to get the result ...
- 13
1
vote
0
answers
1k
views
Update Quantity Available using Quantity from another table
You will demonstrate an update that includes two tables and uses a subquery.
Make sure to prove that your update executed correctly by showing data in the tables before and after the update.
...
1
vote
0
answers
94
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 ...
- 11
1
vote
0
answers
95
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,531
1
vote
0
answers
1k
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 ...
- 111
1
vote
1
answer
157
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 ...
- 13
1
vote
0
answers
35
views
Getting Available Date/Times For Attendees And Avoid Conflict
+------------+ +---------------+ +--------------+
| attendees | | attendee_times| | meeting_times|
+------------+ +---------------+ +--------------+
| attendee_id| | at_id | | ...
- 646
1
vote
2
answers
83
views
Trying SQL query with subquery in WHERE clause
For a message system we would like to know if a conversation allready exists in the database. The user will then be posting to the existing conversation instead of a new one.
Now, we have linked all ...
- 113
1
vote
2
answers
5k
views
MySQL Correlated Subquery (with multiple columns) per row
MySQL documentation states that:
Subqueries in the FROM clause cannot be correlated subqueries. They
are materialized in whole (evaluated to produce a result set) during
query execution, so ...
- 235
1
vote
1
answer
555
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 ...
- 13
1
vote
2
answers
129
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 ...
- 11
1
vote
0
answers
227
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 ...
- 111
1
vote
0
answers
64
views
MySQL - SELECT Columns from results of another Query [duplicate]
I'm trying to use a dynamic range of columns fetched from another query, specifically I want the results of this query:
SELECT COLUMN_NAME as DYNAMIC_COLUMN FROM information_schema.columns
WHERE ...
- 11