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.
-2
votes
1answer
29 views
Subquery and subselection for Count/Number doesnt work
for example, for company (3) and his partners, I need the number of events with payouts. But i get only the number for one partner, im my example i have
i have 3 tables (t_company, t_partner, t_event,...
-1
votes
1answer
31 views
Please help me figure out this data. I am trying to calculate CTR(click through rate). Should I be grouping by the CampaignId?
my data looks like this:
I am trying to calculate CTR, but nor sure how to group it to show some kind of KPI (also open to suggestions on what should I present).
SELECT click_n, imp_n, CAST(((...
0
votes
2answers
28 views
Performing query based on another query's result
First I have to run the query (1) using the pagedetailid number to get the adblockid. Then I have to put that adblockid into the other query (2) to get the result. What I would prefer is a single ...
0
votes
1answer
64 views
Finding the last record within three years of the first record with subqueries [closed]
I have the following table with student enrollment data:
CREATE TABLE [dbo].[Enrollments](
[SequenceID] [bigint] NULL,
[SchoolYear] [int] NULL,
[StudentID] [varchar](8) NULL,
[...
5
votes
2answers
150 views
How to apply ORDER BY and LIMIT in combination with an aggregate function?
A fiddle for my question can be found on https://dbfiddle.uk/?rdbms=postgres_10&fiddle=3cd9335fa07565960c1837aa65143685.
I have a simple table layout:
class
person: belongs to a class
I want to ...
2
votes
1answer
61 views
Updating a table with duplicate id's while avoiding “ single-row subquery returns more than one row” [closed]
I'm currently making SQL that'll update data from 1 table, based on data from another table for my company
update table1
set column1 = (SELECT color
FROM table2
Where ...
0
votes
1answer
26 views
How to select events based an adjacent times?
I have a table rooms which contains information regarding events that take in rooms. This is the definition of the table:
create table room(
sdate date,--start date
stime time,--start time
rno ...
0
votes
1answer
51 views
How to make subtraction without repeating the subqueries? [closed]
I need to efficiently obtain:
players who play more than one world cup and who have never been champion.
primary-key | foreign-key | pk-and-fk
PLAYER<player_id, player_name, country_player, .....
1
vote
2answers
61 views
How to use GROUP BY in an efficient way without losing attributes? [closed]
I am working with PostgreSQL.
My idea is to group players who appear more than 3 times in table-a.
table-a = <player_id, year_world, c, d, e>
With the first subquery I get player_id and the ...
3
votes
2answers
73 views
case statement sub query need to return multiple values
Below query need to return multiple values
select
CASE WHEN LTRIM(RTRIM([Order].ClientProject_Id))!=''
THEN (SELECT StationeryClientProject.ImpFile from StationeryClientProject
WHERE ...
1
vote
1answer
38 views
Logic of Assigning values to variables in Scalar-valued Functions
I am currently working on updating old scalar valued functions with the hope of making them more efficient or changing them into Inline TVFs.
While troubleshooting my rewrites I came across some ...
2
votes
1answer
26 views
Consults and counts in multiple databases
I'm not very familiar with mysql advanced queries, because I'm more of a designer as a developer.
My problem - I have 3 tables:
Clients
------------------------------------------------------
| id | ...
0
votes
0answers
64 views
Query to Calculate Consumption from Reading Table
I have a Reading Table with following details:
Reading(rea_ide(PK), rea_ini, rea_dat, met_ide(FK))
I also have a Meter Table
Explanation:
The bill is received for a ‘Main Meter’ and then this bill ...
0
votes
0answers
35 views
Divide and Update the Hours in SQL Server
I have two tables:
1. ForemanReports having columns ID, Date etc.
2. FRToMainsInstallation having columns ID, ForemanReportID(Primary ID of ForemanReports Table), Hours.
ForemanReports:
ID &...
0
votes
2answers
34 views
Is it possible to have both a subquery and a function in a WITH clause?
In Oracle 12c, we can include subqueries in the WITH clause:
WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc....
0
votes
1answer
21 views
MySQL select all records unless user must be present in favorites
I am having trouble to find the best solution for the following problem;
Description: I want to select all records from an items table, unless it is for favorites only, in that case the user must be ...
1
vote
1answer
30 views
Update value with nested subqueries
I am working in Oracle and trying to update a value in tableA to = 'Y' based on info from two other tables (B and C)
I have tried something like this which does work if I have only the first select ...
0
votes
2answers
34 views
handle subquery with joins where same result is to be joined with multiple tables
I have this query which is going through table B twice I want it go through tableB once as I should be able to select all necessary fields from a table once and use that selected result table in ...
0
votes
3answers
42 views
Optimize query to filter active IDs for a given day from big table
I have a PostgreSQL table called users with 4 columns:
user_id integer, -- we can have a lot of the same user_id
name text, -- name of the user
value text NOT NULL, -- ...
0
votes
0answers
31 views
How can I decrease my running time? - using MS Access sub query with 480 rows
Im tasked with creating a budget tool in MS Access 2013 for which it shows a 'dynamic budget', meaning the leftover budget should be divided over the months left in the year. Should there be any ...
-1
votes
1answer
196 views
Replace Subquery with JOIN - MYSQL
Can you please suggest how i can replace subquery in fetching select columns to improve the performance of query :
SELECT sa.id AS patAppIdSA,
sa.rte_id AS patRTEId,
sa.case_type_id AS ...
1
vote
1answer
33 views
Very weird result of CTEs and subqueries [closed]
WITH t1 AS (SELECT a.id,
AVG(standard_qty) std_avg,
AVG(poster_qty) pos_avg,
AVG(glossy_qty) gloss_avg
FROM accounts a
...
2
votes
1answer
52 views
Deduplicate Data Using Having with Subquery
I've inherited a DB that was lacking a unique constraint so data was/is duplicated. I'm now trying to remove the duplicate records, then add a constraint to block this going forward.
I have this ...
1
vote
1answer
90 views
Why does `row_to_json(…)` accept a sub-query?
I recently encountered this example (via a tutorial and other answer):
SELECT row_to_json(x)
FROM (SELECT id, title FROM my_table) x;
I'm confused about the use of row_to_json(x). I thought it ...
0
votes
1answer
71 views
MS Access M:N Relationship query: exclude records with more than the requested values
I have a little problem when looking up records that have m:n relationships to other tables and therefore appear multiple times in link tables. I try to show it with a minimal example here:
Let's ...
1
vote
1answer
54 views
conversion of a non correlated sub query to a join statement
Is it possible to get rid of the sub query and convert the entire query into a JOIN query?
SELECT student_id,
COUNT(attendance)
FROM student_attendance
WHERE attendance = 'ABSENT'
GROUP BY ...
0
votes
1answer
184 views
Trying to run query and getting error: Error 1064 (42000)
I am trying to get a list of users from 2 different tables. I am using a sub query and the 2 separate queries run fine.
I am getting this error:
ERROR 1064 (42000): You have an error in your SQL ...
1
vote
2answers
37 views
MYSQL Update data based on top 10 records of each user
I have a table users and other table user_scores. In user_scores, I save daily score of every user based on a algorithm. Now I want to calculate AVG user_score by only considering latest 10 records of ...
1
vote
1answer
76 views
multiple insert queries after a set of with's
I am trying to have a reproducible way to populate a database with some test data.
Suppose (an overly simplified example with) three base tables: name, city, job, and two relationship tables: name-...
-1
votes
1answer
22 views
Ambiguous syntax for IN clause
I am working on some select query and I found this syntax
SELECT NOM
FROM INGENIEUR A
WHERE 7 IN (SELECT CODEPT
FROM EMPLOI
WHERE MLE = A.MLE)
I have never seen this ...
1
vote
1answer
18 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....
1
vote
2answers
906 views
user parent table id in nested subquery
SELECT users.*,(SELECT COUNT(user_id) AS mutual_connection FROM
(SELECT user_id
FROM (
SELECT sender_id AS user_id
...
0
votes
0answers
69 views
How to reference column in outer scope
I have some dynamic SQL that finds many table names from a working function, and then passes those in as parameters to format() to produce a long set of union statements. The code that I'm trying to ...
1
vote
1answer
82 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 ...
1
vote
0answers
93 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.
...
0
votes
1answer
34 views
Comparing the location of values in different tables
New to SQL. Hope to find distinct pizza sold by restaurants that are located in the same area as bob (customer).
Database schema:
Customers(cname, area)
Restaurants(rname, area)
Pizzas(pizza)
...
1
vote
2answers
186 views
how to add loop in date column in sql through query? [duplicate]
If ID = 01234 and this ID has done transaction in 3 time in a month like this:
***DAY_no Balance***
1/1/2018 5000
10/1/2018 10000
15/1/2018 ...
0
votes
1answer
16 views
Using OLAP-specs to compute selective running totals
Let's say I have a table with columns id, subid, state, value, where (id, subid) is a unique tuple, state may be 0 or some value <> 0, and value is just some number. I want to build a query that ...
1
vote
1answer
363 views
SQL - sum columns with same name from diferent tables
I have 3 Tables:
_tbl1_____
ID C1 C2
-- -- --
AA 2 3
BB 10 12
CC 20 22
_tbl2_____
ID C1 C2
-- -- --
BB 10 12
AA 2 3
CC 20 22
_tbl3_____
ID C1 C2
-- -- --
AA 0 0
...
0
votes
1answer
29 views
unable to add limit to the subquery
its my first question, please bear me.
I have following tables
Item
id
name
description
itemPurchaseDetail
id
item_id(FK)
branch_id(FK)
purchaseRate
date
Stock
id
item_id(FK)
branch_id(FK)
...
0
votes
1answer
32 views
Count total number of nested one to many relationships
Tables: Users, Costs, Payments
Users can have many costs. Costs can have many payments
I want to get information about the user AND count of all payments that they made
This query returns result ...
0
votes
1answer
64 views
Access Sub Query Speed
I have three tables in Access:
employees
----------------------------------
id (pk),name
times
----------------------
id (pk),employee_id,event_time
time_notes
----------------------
id (pk),...
3
votes
1answer
283 views
Query A if there is no B
I have a table with columns ID, TYPE, DATE, ... and I need all data sets with TYPE <> 'A' (case 1) and the data sets with TYPE = A if there is no TYPE = B with the same ID (case 2). The first ...
0
votes
3answers
57 views
Other way to write query
I'm new to sql language and I'm learning about subqueries..
I have one question is there other way how to write that query?
SELECT Employees.FirstName,
Employees.LastName
FROM Employees
...
0
votes
3answers
433 views
Joining two tables by the nearest match
General problem: I want to do a left join on two huge tables, but there is no matching key - I want to join the left table with the "nearest" row in the right table. In this example I want to join ...
0
votes
1answer
312 views
Count up number of rows matching criteria in same table
I'm trying to extract a list of results, with the greatet number of rows found. Basically, we have a table of values, linked with the key link_id_fk. What I want to basically do is look at each ...
1
vote
1answer
37 views
Select the Scheme and Table based on Select result in Postgresql
I have this query on postgree...
SELECT c.schemaname AS MySchema, a.relname AS MyTable, b.attname AS MyColumn
FROM pg_class a
INNER JOIN pg_attribute b ON (a.relfilenode = b.attrelid)
INNER JOIN ...
0
votes
1answer
43 views
Query to find products with all the given attributes
I have a table in mySQL to store product attributes like this:
------------------------------------
| product_id | att_val1 | att_val2 |
------------------------------------
| 1001001 | x202 | ...
0
votes
1answer
54 views
Are there any other ways to get results from Two tables without using Sub Query
I have a Customer table
+--------+---------+
| Id | Name |
+--------+---------+
| 1 | A |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | 3 |
| 6 ...
3
votes
1answer
339 views
Reuse results from correlated subqueries in same query
I have 4 tables (I actually have lots more that I want to do this with...but this is where I am starting).
distr_catalogs: has many distr_catalog_brands, distr_catalog_system_types
...
