1,285 questions
1
vote
2
answers
101
views
Create a new column based on values in the same table
This is an idea of how my table looks -
no
country
language
translatedName
1
c1
en
test1EN1
1
c1
l1
testL1
2
c2
en
test1EN2
2
c2
l2
testL2
This is how the data might look like where a country can have ...
1
vote
0
answers
63
views
Does Self-join require any other field apart from the Primary Key - dbt incremental
I am code reviewing an incremental model in dbt.
The staging model contains a Primary Key called ID. The table resource in our source DB (SQL Server) contains Primary Key called ID. There is a field ...
- 143
1
vote
1
answer
67
views
How to calculate total salary for managers, including their own salary, using a single SQL query?
I have an Employees table with the following structure:
ID
Name
ManagerID
Salary
1
Alice
NULL
90000
2
Bob
1
80000
3
Charlie
1
85000
4
David
2
75000
5
Eve
2
70000
6
Frank
3
72000
I need a single SQL ...
- 11
0
votes
2
answers
209
views
Self-join excluding joining a row to itself
Sometimes you want all pairs of rows in a table: its Cartesian product with itself. That could be done with a join.
drop table if exists #a
create table #a (x int)
insert into #a values (1),(2),(3)
...
- 1,622
4
votes
2
answers
105
views
How to identify cases where both elements of a pair are greater than others' respective elements in the set?
I have a case where I have a list of pairs, each with two numerical values. I want to find the subset of these elements containing only those pairs that are not exceeded by both elements of another (...
- 2,724
0
votes
1
answer
63
views
Join multiple rows from a table in SQL query where the dates and the shifts are the same
I am using this SQL query to collect and analyze shift production data. The result of the query gives me almost what I want but not quite as the infeed, outfeed, and scrap counts are in separate rows.
...
1
vote
1
answer
38
views
How to return name, given an ID Number from same dataset?
I want to write a query that returns names and their best friends name from the table below:
Example FRIEND Table:
FriendID
Name
BestFriendID
1
Abby
2
Bob
3
3
Carl
2
Expected Output:
Name
Bestfriend
...
-1
votes
1
answer
95
views
SQL JOIN to the same table explanation [closed]
I am refactoring some code that was written by another person that is no longer around. In this query a MAX(Date) is being selected, but then joined to they same table via MAX(Locator). The locator is ...
- 11
2
votes
3
answers
128
views
How to performantly self-join same table multiple times with ascending and descending sort order?
Data Definition
I have a status table like so, attributes not relevant to the question are omitted:
id
created
value
1
2024-06-24T13:01:00
error
2
2024-06-24T13:02:00
ok
3
2024-06-24T13:03:00
warning
...
- 666
0
votes
3
answers
65
views
How can I query no two-column period overlaps?
I'm studying Employees Sample Database which has the following table.
create table dept_emp
(
emp_no int not null,
dept_no char(4) not null,
from_date date not null,
...
- 22.4k
-1
votes
1
answer
38
views
Retrieve latest Rows in table [duplicate]
Sample data
AIID
NIC
Status
Remarks
ID
AddedBy
ADT
28
9595
ON THE WAY
NO
8
0
2024-06-06 16:02:33
27
9595
ABSENT
NO
8
0
2024-05-20 16:02:33
26
573
VISITED
NO
10
0
2024-05-09 15:31:55
25
573
ON THE WAY
...
- 1
2
votes
2
answers
135
views
Build Session Logic based on DateTime in Snowflake SQL
I'm trying to build session logic based on DateTime value for a group of columns (like Userid, DeviceId)
For a given userid, deviceid the session can start any time of the day(first event) and
I want ...
0
votes
1
answer
160
views
Can/should a single table subquery be flattened
I was going through my course material for the SQL class that I am taking and it mentioned that in order to make programs run faster and run in one pass using joins verses a subquery. It stated that ...
0
votes
1
answer
212
views
In Entity Framework Core, how to populate self-referencing navigation collection without additional Include (using only entities already tracked)?
I have an entity called Position with foreign key to itself: int Id; and int ParentId;.This gives me two navigation properties: Position Parent; and List<Position> Children;. When I'm loading ...
- 4,370
-1
votes
1
answer
57
views
Self join table where results list the same column twice with different results
edit:
I have a db table with two columns: id and dep, that lists all department names.
is it possible to run a query (either self join or subquery) that returns the dep column twice (dep1 and dep2).
...
- 109
1
vote
1
answer
276
views
Why self-reference a column from the same table in a SQL subquery?
I'm working on subqueries in the Google Data Analytics Course. There are many places you can rent bikes from. These places are bike stations. The query is to calculate the difference between average ...
- 11
0
votes
1
answer
139
views
Custom Join request on LoRaWAN End Node STM32L0
I want to make my end node send join requests to the gateway continuously.
My goal is to send join requests continuously until the end node connected to the gateway, and if the connection is broken ...
0
votes
1
answer
234
views
Issue with StackOverflowError in Spring Boot application during DTO serialization
I am encountering a StackOverflowError in my Spring Boot application when attempting to serialize a DTO class (NodeAttributesDTO) that has a self-referencing relationship. The error occurs during the ...
-1
votes
1
answer
86
views
Prevent double matches on self referencing table
Is there a quick and simple way in SQL to accomplish this, I have drawn up a long way to prevent this, by doing a "nested" self reference. Is there a simpler way?
Issue:
When you are ...
1
vote
2
answers
78
views
R data.table update last values of parameters with some dynamics by id, using shift etc
Here's a data.table, with some parameters for each id by some regular quarterly dates.
It's originally shuffled randomly, but, at first, let say, it is sorted by fab_date and id.
set.seed(1)
dt_to_fun ...
- 175
-2
votes
1
answer
90
views
Get the students which have attempted exam in current year and at least one previous attempt in last 12 months
I have a task to get the common students who have taken the exam in the current month (November 2023) and must have taken at least one attempt in last 12 months as well. So the look back period is of ...
- 15
1
vote
1
answer
115
views
how to find the maximum number of times the customer id logged in consecutively
table
customer id
login
1
2016-03-01
1
2016-03-02
1
2016-03-03
1
2016-03-05
1
2016-03-06
1
2016-03-07
1
2016-03-08
I want to find what is the maximum number of times the customer id logged in ...
0
votes
4
answers
77
views
Selecting records where autoincrement ID is bigger, but date column is in the past
I have a table with two columns. First one is an autoincrement ID column, second one is a datetime column where next data should be in future to the previous. I need to select values where it is not ...
0
votes
2
answers
79
views
Self join inside table by multiple values. SQL, Postgresql
Please help with self join inside one table by different fields. given table "Items":
Used PostgreSQL.
I need to get all the values from such table, for which factId = 'fact:170' and factId1 ...
- 21
0
votes
4
answers
88
views
How to find duplicate items in a table of a database
Suppose, we've a table named college.
In this table we've the following columns: College ID, College name, State and Established in.
Now I want to find the names only of those colleges where the ...
-2
votes
1
answer
82
views
MYSQL with table join on query（Could the query works more efficient?）
I've two tables, the users:
and the scores of exercise of the users, paperId shows the exercise ID, and the firstTry shows if it is the first try (value 0) or not (others value):
For a given ...
- 241
0
votes
2
answers
81
views
POSTGRESQL Self Join Create New Column With Particular Condition
CREATE TABLE accounts (
"id" INTEGER,
"parent_account" INTEGER,
"merchant_type" VARCHAR(8),
"name" VARCHAR(32)
);
INSERT INTO accounts
("id"...
- 119
0
votes
2
answers
502
views
Row-level calculations within a windows function?
I'm working on this problem from LeetCode.com:
Column Name
Type
machine_id
int
process_id
int
activity_type
enum
timestamp
float
The table shows the user activities for a factory website.
(machine_id, ...
0
votes
1
answer
53
views
Data model line pointing to itself
I am learning SQl on microsoft learn.
During an exercise Microsoft provided a datamodel. On the bottom Right there is a line with a key pointing to itself. What does that mean?
- 121
0
votes
1
answer
107
views
Self join - update a variable - proc sql
I have this table:
data MyTable;
input Household age gender $ flag;
datalines;
1 45 male married
1 35 female married
2 50 female married
2 52 male married
3 32 male child
3 50 male married
3 ...
0
votes
1
answer
86
views
Query to Aggregate Cumulative Quantity of Parent and Child Entity Data
How can I make a query that aggregates data from entities and their children down to the lowest level?
I have two tables.
The first is tblEntity, a self-join table with entity and parent entity ...
- 1
0
votes
1
answer
44
views
how to phrase a conditional condition?
I want to query a self join that will only join on the first condition and only if it cant find a match it will join the second condition
I tried this syntax which doesnt work for me, because it will ...
user22021425
0
votes
1
answer
548
views
Query with self-joins
I have purchases, which may (or may not) have an associated sub-purchase, which i've modelled with a self-join as:
class Purchase < ApplicationRecord
has_one :sub_purchase, class_name: 'Purchase',...
- 180
1
vote
1
answer
62
views
R data.table rolling join etc to add dynamics to previous period
There is a data.table, which has some data for different id's on yearly basis:
example_data <- data.table(
id = c('id1', 'id1', 'id1', 'id2', 'id2', 'id2', 'id2', 'id2'),
year = c(2010, 2011, ...
- 175
0
votes
1
answer
79
views
Updating Rails record with self-join association doesn't update self join column
For my Employee class, I have the following self-joinig associations:
class Employee < ApplicationRecord
belongs_to :replaces_employee, class_name: "Employee", optional: true
has_one ...
- 47
1
vote
1
answer
45
views
Can't understand the mysql self left-join query
I come across a query which is,
SELECT it1.survey_set_id, it1.type, it1.value FROM survey_condition_filter it1 LEFT JOIN survey_condition_filter it2 ON(it1.survey_set_id = it2.survey_set_id AND it2....
user15980977
1
vote
1
answer
67
views
Executing two self-joins after another to filter on two columns in a data frame
Let's assume the following dataframe:
library(dplyr)
dat <- tibble(
Structure = c("A", "B", "X", "A-X", "B-X", "C-X", "A-Y"...
- 2,106
-2
votes
1
answer
163
views
What would happen if I do a self join of a dataframe and also apply broadcasting?
What will happen if I join a dataframe/RDD/dataset with itself, i.e. do a self-join, and do a broadcast of the same dataframe/RDD/dataset in the operation?
The broadcast and self-join can't work ...
- 11
-3
votes
1
answer
219
views
How can I combine two virtual tables from one table, assigning half the data to a new column, without losing data
I have a table formatted as such
age
height
name
15
180
george
16
192
phil
20
148
lily
17
187
george
19
196
phil
24
147
lily
19
190
george
20
199
phil
22
148
lily
21
190
george
27
197
phil
60
138
lily
...
- 517
-1
votes
1
answer
96
views
How to create combination between family members from one table
I have one table with 4 columns. The first column contains Group_ID. The 2nd column contains the Contact_ID. Columns 3 and 4 contain First Name and Relationship (respectively). I would like to show ...
- 1
-1
votes
1
answer
63
views
That in self-joining, each product forms a pair with itself ("A-A"), and each pair occurs twice ("A-B" and "B-A"). Get rid of these two problems [duplicate]
select * from (
select * from (
select *, unnest(product_ids) as product_id
from orders
) t1
inner join (
select *, unnest(product_ids) as product_id_1
from orders
) t2 on t1....
0
votes
1
answer
930
views
I need to loop through the Sql table until a certain condition is met
I am trying to find a supervisor ID of an employee based on the organization unit.
Table A: Employee
EmpID
BeginDate
EndDate
OrgUnit
1001
20070618
20070624
5001
Table B: OrgUnit
OrgType
OrgUnit
Code
...
- 21
0
votes
1
answer
77
views
Is double joining this table a good idea in this case? [closed]
I have the entity Event which will have 1 or more EventDay(s), that will represent a contignous time period in a given day. e.g. Event A has 2 EventDays:
EventDay 1 - date: 10.10.2000; startingAt: 10:...
- 187
0
votes
1
answer
49
views
ManyToManyField self symetrical unique together relation
I have some trouble trying to fetch some manytomanyrelated data in by Django Application
Here is my model :
SoftwareVersion(models.Model):
id = models.AutoField(
primary_key=True,
...
- 1
0
votes
0
answers
53
views
Self join in Asp.net Mvc using LINQ query to get a result using two rows in the same table
I want to fetch records from a table.
I have 2 records for each invoice in a table and I want to join them so that I can show 1 record.
The situation is something like this: I have 2 invoice records ...
-1
votes
2
answers
43
views
Update missing values in MySQL 5.6 table by selecting values from closest records with same id
Let's say I have a table with logs. The value of status column may be NULL and I would like to UPDATE this value by copying value from the log for same settingid which have non-NULL status and closest ...
-1
votes
1
answer
69
views
How do I count by grouping a value without other select terms influencing the count? (self-join)
So, I want to show which employees (names) have subordinates and how many subordinates they have (I was using self-join).
It works fine if I use count and group by looking at the "manager_id"...
0
votes
1
answer
125
views
MySQL query with self-join scans millions of rows
I have an application to work on employee shifts for factories. I have a SQL query to find out if shifts have conflicts: if you have a shift 08:00-16:00 and I assign another shift 09:00-17:00 on the ...
- 1,169
-2
votes
1
answer
85
views
Top game with highest sales for each year [closed]
I'm using SQL Server with a table about video game sales from the years (1977-2020). The query below is what I've been using to solve the problem: finding the #1 game with the highest sales for each ...
0
votes
2
answers
130
views
Is there a way to collapse ordered rows by terminal values with postgres window clause
I have a table foo:
some_fk
some_field
some_date_field
1
A
1990-01-01
1
B
1990-01-02
1
C
1990-03-01
1
X
1990-04-01
2
B
1990-01-01
2
B
1990-01-05
2
Z
1991-04-11
2
C
1992-01-01
2
B
1992-02-01
2
Y
1992-...
- 35