Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

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 ...