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.
0
votes
1answer
8 views
why is this postgresql query returning only zeros?
select g.ad_id, count(1) as total,
(select count(1) from geoData g1 WHERE g1.ad_id=g.ad_id and
utc_timestamp > '2017-11-01 00:00:00' AND utc_timestamp < '2017-11-01 23:59:59') NOV1,
(select ...
0
votes
2answers
35 views
Stored procedure that insert data and return value [closed]
can anyone fix my problem ? i wanted to return the value "SERIAL EXISTING" but i can't make it happen. i already doing it overnight.
CREATE PROCEDURE ADDHARDWARE
@serial varchar(100), @location ...
1
vote
2answers
32 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 ...
-1
votes
2answers
37 views
Group by in subquery
So my problem is the following. Consider this query:
SELECT
P.id,
(SELECT barcode FROM product WHERE publisher_id = P.id LIMIT 1) AS barcode_sample
FROM
publisher P
WHERE
P.name LIKE '...
2
votes
2answers
119 views
Subquery gives no error for a non-existing column with the same name as in the outer query
I have two tables in a MySQL database - t1 with a column c1, and t2 with a column c2.
I run this query:
select * from t1 where c1 in (select c1 from t2);
The above query should give an error as c1 ...
1
vote
1answer
9 views
Query for existence of objects across two multi-to-one relationships
I have three relations. The first is "MyObject", which contains an integer "value" attribute as well as a foreign key to "Config". The relationship is "multiple MyObjects to one Config".
The "Config" ...
0
votes
2answers
39 views
How to select specific items from table A which appear with all items of some column in table B?
I have 2 tables with the following schemas:
t1(id, type) where id and type are the primary key
t2(type, rate) where type is the primary key
I want to find all id's which appear with all types from ...
1
vote
1answer
39 views
Mysql subquery not recognizing the subquery join table field
I have a three tables. The main table is the journeydata and next table is the alarm tables. The primary key for journeydata is journeydataID and its available as key in the alarm tables. Next in the ...
1
vote
0answers
28 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 ...
1
vote
0answers
69 views
Why is Postgres choosing a Hash-Join instead of a Nested Join that is 200x faster?
I run the following query against Postgres 9.5 which returns article information along with the currently valid price:
WITH selected_article_rm(id) AS (
VALUES
('80512e52-a406-4a9b-a8df-...
-1
votes
1answer
65 views
Randomly select 4 items of a randomly selected category
I need an idea how to construct this query.
I have a web page. each time the page is refreshed, I want to select data from a random category, and to select no more than 4 random items from that ...
0
votes
4answers
33 views
filter out the records with same id having different values in a column
Can anyone help me to solution for below issue.
create table [order1] (order_id int, CartTransaction_Id int, Status_Id int);
insert into [order1] values
(357488,87214,1),
(357489,87214,8),
(357490,...
-2
votes
1answer
32 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
36 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
50 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
70 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
185 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
244 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
54 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
65 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
404 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
47 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
82 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
38 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
36 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
23 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
32 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
43 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
38 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
352 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
84 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
183 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
96 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
306 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
47 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
79 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....
2
votes
2answers
2k 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
109 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
94 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
142 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
586 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 ...
