1,285 questions
1
vote
2
answers
100
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
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
4
votes
2
answers
102
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
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
...
2
votes
3
answers
126
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
-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
0
votes
1
answer
159
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 ...
-1
votes
1
answer
53
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
0
votes
1
answer
137
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 ...
-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 ...
-2
votes
1
answer
89
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
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
4
answers
85
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 ...
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
1
answer
49
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
76
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
543
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',...
- 170
0
votes
1
answer
76
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
61
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
-3
votes
1
answer
212
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
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
76
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
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
1
answer
67
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"...
-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 ...
