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.
509
questions
0
votes
2answers
27 views
Select/print data from subquery
In my application I have a table "user", a table "role", and a table "userroles". Userroles is simple table consisting of user, role, where user corresponds to the id column of the user table. As user ...
0
votes
1answer
27 views
Optimize slow COUNT in MySQL subquery
I have the following query that takes minutes to complete:
SELECT DISTINCT Designation, Model, (
SELECT COUNT( Equipment.EquipmentID )
FROM Equipment
INNER JOIN EquipmentDesignation_Vw ...
-2
votes
3answers
65 views
Can a `Select` Subquery be conditionally returned?
Is it possible in Postgres to return a column(Resulting from a subquery) only when a condition matches?
Query:
SELECT
(SELECT NOW()) AS FIRST,
(SELECT NOW()) AS SECOND
Outputs:
| first | ...
0
votes
1answer
45 views
Can we use CTE and Subquery together?
with sum as (
select ud.id,sum(data_volume) total
from mediation.udr ud
inner join mediation.meter_reading mr on ud.cdr_id = mr.id
group by ud.id
)
(select total from sum)
select act....
-4
votes
0answers
53 views
how can implement the subquery that shows me one table exactly like this picture [on hold]
How can implement the subquery that shows me one table exactly like this picture?
I try this query.
I want separate month and year and bring month into the separate column just like the pic Q 3ai
...
0
votes
0answers
17 views
Postgres Pass Subquery results to function that returns TABLE
I have a function that accepts arrays as parameters and returns a table. I can get the arguments passed in correctly, but it is returning rows of type record. If I try to expand the record with FROM I ...
0
votes
1answer
18 views
Is there a way to alias columns for reference in subqueries?
I have a SQL statement in PostgreSQL that takes the following form:
SELECT * FROM db WHERE (b + 1) IN (SELECT b FROM db WHERE a = 7)
This query returns each record whose b value, incremented by 1, ...
0
votes
1answer
16 views
How to rank an aggregation in each newly formed group?
The problem below that I would like to solve sounds quite simple but it is quite difficult for me to arrive at the solution with SQL.
Let's say I have a simplified ERD that looks as follows:
This ...
-1
votes
2answers
31 views
How to get column values in where condition in sub query? [closed]
I am trying to get distinct list of values in where condition through sub query but it shows 0 rows in result set. Here is my query.
SELECT a.topic_id, t.id as topicid, t.value as value, t.topic as ...
0
votes
1answer
21 views
How to use query result as REPLACE parameters
i'm trying to:
1- Find a string inside a lot of content
2- Replace that specific part of the string with a new one using REPLACE function like this:
REPLACE(column_name, query#1, query#2)
Is this ...
0
votes
0answers
30 views
Two left joins then two on statements [duplicate]
I stumbled upon a left join function that is throwing me off... I was trying to eliminate a subquery or using another temp table. Both of the following queries produce the exact same values.
SELECT *
...
0
votes
1answer
34 views
Query to get the physical file name of the database
Im trying to get the physical file name of a database, and when i query sys.database_files, under the physical file name column, you get the whole path (ie. S:\Program Files\Microsoft SQL Server\...
0
votes
0answers
18 views
Postresql using subquery select in order by clause
Is it possible to order by a selected columnname?
SELECT * FROM tablex ORDER BY quote_ident((
select column_name from information_schema.columns
where table_name = tablex AND column_name LIKE '...
0
votes
0answers
75 views
Subquery caching in join
I have very complex MySQL queries that use the same subquery over and over again. I was wondering if MySQL will run the subquery multiple times or use the previous resultset. Is there any way to tell ...
1
vote
1answer
37 views
selecting data after filtering out
I am using RDBMS MySQL 5.7. I have 3 tables with the following structures:
Table A - primary table - products:
+----+-----------+-------------+------------+--------+
| id | title | description |...
0
votes
0answers
24 views
Finding matching sub values?
So not too sure how to go about this, but ideally I want to pull out matching & non-matching info from an array of rows in a table, so I have a table with info like this:
12,2019-07-15 06:02:52,...
0
votes
1answer
36 views
Problem with How to join with subquery
I'm trying to create a report in Postgresql that shows some columns from some tables with join. But one of them is subquery that to show a customer with the location. My Problem is, I always get ...
0
votes
1answer
40 views
Why last table don't using index for joining?
By the help of others members i get an Query idea for my previous problem. But after writing this, the EXPLAIN showing that my last table do't using index for join but the joining ROW is PRIMARY KEY, ...
0
votes
0answers
21 views
How can i remove filesort for this subqueires?
This is the Query:
SELECT
A.*
FROM
messages_thread_user mtu,
LATERAL (SELECT
m.*, u.userName
FROM
messages m
JOIN
...
0
votes
0answers
59 views
Sql subquery for nested parent child data
I have two tables , on parent table (categories ) and child table (features).. I have this query
select c.* ,
(select GROUP_CONCAT(f.featuresId , f.featuresTitle )
from ...
0
votes
1answer
20 views
MySQL - Optimize subquery in Select, where and order by
Im running a query like below.
SELECT DISTINCT c.`id`
, c.`job-cat`
, (
SELECT COUNT(*)
FROM `jobs` j
WHERE j.`mul_category` IN (c.`id`)
AND j.`ending` >...
0
votes
2answers
36 views
Postgres select using lateral
I'm after running a query with a subquery (perhaps inefficient) that using a regular expression pattern it can return how many instances of a characters are included with a given record.
In doing so, ...
0
votes
1answer
68 views
Query to Find Multiple Start Time and End Time on a Single Day based on Boolean Value
I want to find the multiple Start Time and End time in a single Day based on the Boolean value where data is divided into each half hour interval for a day. So Each day is divided into 24hrs/2 = 48. ...
0
votes
1answer
32 views
How do I create a table with count of instances of an id before an event date that is individual to that id?
I am very new to SQL and have a relatively complex query to write, any help would be appreciated.
I have data with the following columns:
ORDER_ID
CUST_ID
ORDER_DATE
RETURNED(1/0)
I need a table ...
0
votes
1answer
39 views
Slecting specific query returnned data related records from another table [closed]
Product table:
productId | productName | unitPrice | supplierId
_______________________________________________
1 | Iphone8 | 1500 | 3
2 | Nokia 6 | 800 | 1
...
4
votes
1answer
107 views
How to take make OFFSET & LIMIT with COUNT (OVER?) when having a subquery?
It appears I have a case I can't quite wrap my brains around. So coming here in hopes to find pointers to a query that maybe could be helpful to someone else too.
In the following, I have a query ...
1
vote
1answer
56 views
Limit left join for multiple rows
Since MySql doesn't support main alias reference in subquery is a bit challenging to create query with multiple rows where you have to limit rows of left join tables.
Let's overview simple example.
...
0
votes
1answer
28 views
Two aggregated sub queries wont run
I am having trouble with the below, I need to average two columns in a correlated subquery and I cannot get it to work, how do i get both aggregate sub queries to run then go through the outer query? ...
2
votes
2answers
62 views
What am I doing wrong with subquery
I cannot work this out, how do I get the subquery to return the lowest course weight for each lecturer, it currently only returns the lowest and is my subquery wrong or outer query?
Question: Show ...
3
votes
1answer
58 views
Why is an empty rowset not equivalent to null when assigning a query result to a variable and how best to work around?
I have a cursor. Within my loop I select a single column and assign the result to a variable. When the select returns an empty rowset, nothing is assigned to the variable. In particular, the variable ...
0
votes
1answer
66 views
Cannot get a user function to co-operate with a subquery in mariadb
The following is a demo of an issue we are having with a function and a subquery. I'm interested to know if it is a bug or if there is a reasonable explaination why it is failing.
Notes:
Problem ...
1
vote
1answer
116 views
Having a subquery somehow do the same group by as the outer query
i'm pretty decent with SQL but it's not my main experience of expertise. A colleague asked me a stumper of a question the other day. I was wondering if you guys could provide assistance.
Basically we'...
2
votes
3answers
66 views
Return ALL rows based on a matching date also matching a specific value in another column
I'm using SQL Server 2014, exact version: 12.0.5579.0.
I've tried a subquery under a SELECT and WHERE statement and it brings back false results with rows of any row with a 1/1/2019 date. It just ...
0
votes
1answer
105 views
When to use lateral joins vs correlated subqueries in Postgres?
These two queries:
SELECT username, location, task, ts_next - ts AS diff
FROM (
SELECT username, location, task, ts, (
SELECT ts AS ts_next
FROM switches
...
0
votes
2answers
62 views
MySQL Function work very slow
I have writed this mysql function, it's worked perfekt but very slowly, what can I do for better performance, have you any idea?
note:i need all created virtual columns
DROP FUNCTION IF EXISTS ...
-3
votes
1answer
57 views
Print Two Different Columns with Union All- SQL
I have an SQL statement like this,
SELECT *
FROM (select "John" as FirstName Union All select "Mary" Union All select "Alis"),
(select "Doe" as LastName union all select "Queen" union all ...
2
votes
1answer
93 views
Why Subquery do not filter the result in multiple Subquery
This is Query (skipped selection for simplicity and also it work perfect):
SELECT
*
FROM
(SELECT
a.striker
FROM
ball_by_ball a
LEFT JOIN batsman_scored b USING (...
0
votes
0answers
32 views
MySQL How to get first row from subquery when using aggregate function
This is my Query:
SELECT
COUNT(runs_scored) - SUM(IF(extra_type_id = 4, 1, 0)) AS Balls,
SUM(runs_scored) + SUM(IF(extra_type_id IN (2 , 4),
extra_runs,
0)) AS Runs,
SUM(...
0
votes
0answers
14 views
How to find full siblings? I have been able to find the parents, but can't figure out how to join siblings based on having same parents
I have two tables (table/column names not my choice):
| Person | | Parent |
------------------------------ -----------------------
| ...
0
votes
0answers
20 views
Combine multiple queries that each require a row of input values
I'm looking for a way to combine multiple queries that each need two values.
For example, I can look up a patient's age and weight as follows:
select age
, weight
from patients
join ...
0
votes
1answer
31 views
How to search records based on date range for other matching data
I have below records in table
APP_No Stage Start_Date End_Date
A123 L1 01-01-19 02-01-19
A123 D1 01-03-19 05-03-19
A345 L1 01-01-19 02-01-19
A345 D1 01-03-19 05-03-19
I want ...
0
votes
2answers
59 views
How to join these queries?
So I currently have this query:
SELECT v.Voorstellingsnummer, COUNT(r.Voorstellingsnummer)
FROM reserveringen r, voorstelling v
WHERE r.Voorstellingsnummer = v.Voorstellingsnummer
GROUP BY r....
0
votes
1answer
19 views
How to manually set a return value to a subquery with multiple results
I have a subquery which can return multiple results if a customer has duplicated sales codes.
I currently LIMIT 1 to just return the most recent but ... Is there away to return NULL or "DUPLICATE" ...
0
votes
1answer
256 views
Multicondition Subquery Max Date and Item Number
The following is a query to get latest Sales date for a customer.
select pt.CustomerId, pt.SalesDate, pt.*
from dbo.PurchaseTransactions pt
where pt.SalesDate = (
select max(SalesDate)
from ...
0
votes
2answers
201 views
Count rows in a table based on a DATE field
I have a table with 3 columns (order_id, client_id, date_added).
The content would look something like this:
order_id | client_id | date_added
-----------------------------------
14152 | NA4156 ...
1
vote
1answer
39 views
Why Output is wrong after doing the left join as subquery
I have TWO QUERY which work perfect, but when i join those two query as LEFT JOIN the output become wrong. So what is the problem.?
Query 1:
select Batsman.innings_no,
bowler,
sum(...
1
vote
1answer
27 views
Using a Calculated Field in a Separate Calculated Field
I'm trying to avoid creating a temp table for just one single field, but I need to use a calculated field in a separate calculated field. For example:
,CASE WHEN ln.INT_RATE_CODE = 'A' THEN 1
...
5
votes
1answer
60 views
Why an incorrect JOIN using correlated sub-query is so much slower
I'm doing some fairly lightweight data massaging/cleaning and ran into a problem where one version of JOIN using a correlated sub-query (probably an erroneous one) ran much much slower than what I ...
-1
votes
1answer
43 views
How to do group by based on another column in SELECT? [closed]
I have a table on which I do a SELECT statement. It is joined to some other tables. With the SELECT one column is got but a sub query. like (select top 1 .. from..) for each row. Now I need to do an ...
0
votes
0answers
30 views
Comparing count of 2 tables, is this the correct way?
I'm building a survey system where
A client that has many users
A user can have multiple surveys.
I have a table users and a table survey_completions, I'm wondering if this is the correct (or best) ...
