Questions tagged [self-join]
The self-join tag has no usage guidance.
50 questions
1
vote
1
answer
39
views
Issues with Self-Referencing Foreign Key in SQLite
I'm having trouble with a self-referencing foreign key in SQLite. I've created a table to store employee details, where each employee can have a manager who is also an employee. Here's the table ...
- 111
1
vote
1
answer
25
views
Multiple parents for AspNetUsers table for Asp.Net Core 3.1 app
I have the following table in MySQL.:
CREATE TABLE `aspnetusers` (
`Id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`UserName` varchar(256) CHARACTER SET utf8mb4 ...
- 11
1
vote
1
answer
106
views
Bidirectional self join table
Consider:
CREATE TABLE object (
id integer NOT NULL DEFAULT nextval('object_seq'),
...,
CONSTRAINT object_pk PRIMARY KEY (id)
);
CREATE TABLE pairing (
object1 integer NOT ...
- 61
3
votes
1
answer
356
views
Return recursive self joins in PostgreSQL with aggregated data
I have this simple table schema:
SQL Fiddle
PostgreSQL 9.6 Schema Setup:
CREATE TABLE IF NOT EXISTS users(
id INT NOT NULL PRIMARY KEY, email TEXT NOT NULL UNIQUE,
password TEXT NOT NULL, ...
- 165
0
votes
0
answers
675
views
T-SQL, Fast self join on the next available date where dates skip weekends and multiple rows can have the same date
TL;DR,
I'll post back later with more info including table, view, and index definitions; before/after results; and query plans to help to answer this question.
I'm trying to use a self-join to a view ...
1
vote
1
answer
915
views
MySQL Indexes on all column combinations
I have a table that basically represents the log of records
CREATE TABLE records
(
id varchar(36), -- uuid
text blob,
user_id bigint,
cluster_id bigint,
status_id ...
- 11
0
votes
1
answer
644
views
How to optimized self-join view of 50 millions rows table
I've the following simplified table with ~50M rows.
table sample (
id uuid not null primary key,
measured_date timestamp with time zone not null,
segment_id uuid not null,
activity_id ...
- 135
2
votes
1
answer
612
views
Best practices for large JOINs - Warehouse or External Compute (e.g. Spark)
I am working on a problem that requires a very large join. The JOIN itself is pretty straightforward but the amount of data I am processing is very large. I am wondering for very large JOINs, is there ...
- 543
0
votes
2
answers
112
views
Improving query (Doing a nasty self join on a range with ctes)
-- The CTE1 is just to create columns that dictate the bound of what is considered the same entry
-- Also I do a dense rank by ACT_TIME, and a PARITION BY on ID1, ID2
-- so all ID1/ID2 combos are ...
- 103
0
votes
1
answer
464
views
Self Join a Table to Get a Pair of Value But Could Not Receive the Expected Results
I am trying to self join the relation casts(movie_id,cast_id,cast_name). I want to query one pair of actors who have appeared in at least 1 movies together. The self pair must be excluded, and keep ...
2
votes
0
answers
898
views
SQL Server - Self join on table is very slow
Working with Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64)
We have a single table, which is planned to contain about 1.5 - 2, maybe max 3 million records at a time. Records will ...
- 163
0
votes
0
answers
1k
views
Flatten multiple rows from One to Many to single row
This questions is similar to what I need but the answer seems rather specific to the question rather than a general answer.
One to many relationship query result in a single row and dynamic multiple ...
- 23
0
votes
0
answers
22
views
How do I join rows from the same table with slightly different info?
We have a new sector of our company that is being put into our employee record feed. However this sector is listed as contractors as there are still parts of the sector that need access to systems ...
- 551
0
votes
2
answers
44
views
T SQL, Get new customer added or deleted from historical table
I have a Table containing historical data. Every day the same staff list will be added with the new dates (between 1200-12500 staff each day however only a few staff might be newly added todaysDate or ...
- 3
0
votes
2
answers
112
views
Explaining what the OR operator does in the following code
I ran into the following SQL code which (successfully) outputs a list of parents and the age of their youngest child:
SELECT parents.name AS name, MIN(children.age) AS age FROM people children
INNER ...
- 103
0
votes
1
answer
573
views
Multiple self joins where joining condition not always exists
I am trying to perform a multiple self join query where one of the conditions it is joining on does not always exist. I have a table that stores different sale events, where a single transaction (...
- 3
0
votes
1
answer
397
views
MariaDB: Why is this still using temporary and filesort - is it because I'm using a self-join? How to fix?
SELECT e2.personId, COUNT(*)
FROM entries e1
JOIN entries e2 ON e2.categoryId = e1.categoryId
AND e2.personId != e1.personId
WHERE e1....
- 265
0
votes
2
answers
91
views
T-SQL: how to show the corresponding column's row to an aggregating function's result?
Everyone,
I have a question - how to show the (possibly ambiguous) column that corresponds to the value retrieved by an aggregating function?
Related to the dataset below, the task is to find the ...
- 151
0
votes
1
answer
264
views
SQLite - Counting referenced rows in one table using a different, related self-referencing table
media_subscriptions
+----+--------------------------+
| id | target_metadata_item_id |
+----+--------------------------+
| 30 | 19039 |
| 31 | NULL |
| 32 | ...
0
votes
0
answers
95
views
Matching columns within the same table in SQLite
I have a table of Employees which contain, among other things, the employeeId, the lastName and the ReportsTo column, that refers to the manager ID to which the employee has to report. That manager is ...
1
vote
2
answers
47
views
How to optimize selection of pairs from one column of the table (self-join)?
I'm using PostgreSQL 9.5.19, DBeaver 6.3.4
I have a table where one row is - user's name, place he attended, time when he was there
I need to select all pairs of places where any user was (if user ...
- 165
0
votes
1
answer
316
views
Group by unknown ID, select all related rows where Column X = Y and Column X = Z
I have a table that logs interactions from users with the following structure.
SELECT [SessionID]
,[Question]
,[Response]
,[DateCreated]
,[Terminal]
FROM [Quiz]
where Teminal ...
- 137
7
votes
1
answer
1k
views
Recursivly get a Tree Via self joined table
Using other questions here and Postgresql documentation I've managed to build a many-to-many self joined table.
However adding a WHERE clause is giving me trouble.
Problem:
A Category can have many ...
- 171
0
votes
1
answer
131
views
accelerating a SQL quadruple self-join with a complex alternation in the WHERE clause
The following Sqlite query has a triple self-join on table t:
SELECT "update_variable",
lhs_acc.name_suffix || ":" || rhs_var.name_suffix,
op.span,
op.path
FROM t op
JOIN t lhs_acc ON (...
- 121
0
votes
1
answer
28
views
Join a table to itself, each row needs to join to the ordered last record
I have a logging table which for whatever reason logs the column and table that changed, the date it changed and also the value it was changed FROM (the old value). I want to join these records ...
0
votes
1
answer
18
views
Selecting Multiple Values from Same Coulmn
I want a query to find out who doesn't have a specific role (user, create or admin)
DB is Structured as Follows:
name_last | name_first | role
Sample Data:
brown | john | user |
brown | john | ...
- 1
1
vote
1
answer
37
views
How to retrieve data from a table with two of the columns in the table with the same value?
UserName, UserID, FollowingUserID
'RachelLim', '1003', '1004'
'YunaKim', '1005', '1007'
'AustinLee', '1007', '1005'
'BenChoi', '1010', '1001'
'RebeccaFang', '1009', '1004'
'AliceAng', '1004', '1003'
...
- 11
1
vote
2
answers
4k
views
Select the students that are enrolled in both the courses [closed]
This is the table definition. I want to select the students that are enrolled in courses (1 and 3).
From this dataset the expected output is 'B'.
- 113
0
votes
1
answer
561
views
write SQL query that takes its own output as input
In a table that displays how trades are related to each other, starting from one trade, I want to find all other trades that relate to the first trade:
713613 PostPTETradeIDs 718478
713613 ...
- 101
5
votes
1
answer
739
views
Is it possible to upsert into a self-referencing table with a single statement in PostgreSQL?
If I have a table A, like so:
A {
id SERIAL
title TEXT
...
parentId INT references A.id via a foreign key constraint
}
I am pulling data from a source table - A_SOURCE - where there isn't a ...
- 2,410
0
votes
0
answers
134
views
Postgres count the same join by different values
I've been trying to create a query that counts the number of different types of events associated with each entry in another table
city job job_events
id | name | state ...
- 621
0
votes
3
answers
3k
views
Usage of aggregate function with when self-joining a table
I want to find people from a database who has a weight bigger then the average weight:
SELECT *
FROM Player p1, Player p2
WHERE p1.weight > avg(p2.weight)
But I have an error:
Result: misuse of ...
1
vote
1
answer
85
views
Select most recent event for each plate on a property unless the most recent is "Out" - self-join?
My cameras are positioned on the entry and exit gates of a parking garages. As cars pass the cameras, they capture the license plate and other attributes and load it into a database in real time. I ...
- 13
6
votes
1
answer
411
views
Self-join example review in Books Online
Here in this example it states that performs a self-join, but it joins Sales.SalesPerson to Sales.SalesTerritory. I knew that self join means joining the same table to itself but here aren't Sales....
- 2,142
-2
votes
2
answers
446
views
Can a transitive join loop infinitely?
I have a table that stores alternative product:
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------...
- 99
3
votes
2
answers
1k
views
Joining two ltree based tree tables recursively
I have two tables, roles and permissions, both of which use ltree for maintaining a tree structure. I also have a pivot table, roles_permissions, which serves to connect the two tables. How can I join ...
- 131
3
votes
1
answer
7k
views
Avoid duplicates on self join
Given the following table my_table :
my_column
------
A
B
C
D
I would like to be able to join on itself but without duplicate pairs like so:
-- SELECT a.*, b.* FROM my_table a JOIN my_table b ON a....
- 133
-2
votes
1
answer
2k
views
how to make sql statement to find siblings in a table [closed]
I have a question here which need your kindly help.
I have a table with the following data
family_id,student_Name
--------- ------------
A,Tom
B,Jack
C,Steve
B,Terry
C,Jerry
And then I want to write ...
- 17
0
votes
1
answer
86
views
JOIN by part of two column names
Consider a table with the structure of
id col1
WqDs.first
WqDs.second
eeRT.first
eeRT.second
How can I JOIN the table to itself using the first part of the id
SELECT idxx, a.col1 AS ...
- 4,531
0
votes
1
answer
4k
views
Inner join on output of a subquery
We have Slot table which tells us about freetime for an employee between starttime and endtime.
We need to find Slot which have freetime > 'x' mins. This could be either be a complete slot or it can ...
- 299
-2
votes
2
answers
616
views
Nontrivial joining a relation with itself [closed]
I am a philosopher and am doing my first steps in relational algebra. So here is a puzzle:
Let say that we have a relation R with attributes 'R.a' and 'R.b'. Is it possible to make a renaming and ...
- 1
1
vote
0
answers
561
views
How to get the hierarchy of particular employee using JPA spring boot?
I am trying to find a way to get the employee hierarchy in an organization using JPA and Spring boot.
The problem is similar to the following link
Similar Data Model
If A reports to B and B reports ...
- 11
2
votes
1
answer
61
views
Looking for help reporting duplicates and null marks
Someone over at StackOverflow suggested this might be the place to ask this. I hope this will question is proper for this forum. I am looking for suggestions / criticisms of alternative methods of ...
- 135
1
vote
3
answers
1k
views
Many-to-many tag filter query with a junction table get slower with more tags. Can it be faster?
Part 1
I have this query to find some videos containing all given tags. Here with tags 1,9,27,13,67 as an example.
SELECT *
FROM video v
WHERE v.id IN (
SELECT tr1.vid as video_id FROM (SELECT ...
- 187
1
vote
2
answers
920
views
Update using the value from the closest row
My table looks like this:
-----------------------------------------
id | name | housename | houseno
-----------------------------------------
1 | John | John's House | 2/22
---------...
- 155
4
votes
1
answer
4k
views
Joining multiple subqueries
I need a query that returns something in this format:
pk id v1 v2 v3 v4 v5
----------------------
...
pk1 id1 A1 A2 A3 A4 A5
pk6 id2 B1 B2 B3 B4 B5
...
My current data looks like this
CREATE ...
- 141
1
vote
2
answers
7k
views
Filter one column by value in second column postgres
I'm constructing a large table that is pulling values from across the database. Two of the fields I want to include in the new table are stored in a single column in another table. I want to split ...
- 281
3
votes
1
answer
3k
views
Help on Self-Referential CTE SQL SERVER
I have this table below which is has a hierarchy to it where the number of levels vary, for this example I chose a subset with 7 levels.
L_Key Parent Level P_Key
-------------------------
1393 ...
- 53
0
votes
1
answer
212
views
Want to perform a function on a returned table on subset of rows
I have a query that takes 10 minutes to run (1.4 million rows and 15-ish joins), so I think the time isn't terrible.
What I need from this is to effectively INNER JOIN on itself an additional column ...
- 15
10
votes
2
answers
3k
views
self-joins in recursive CTE: `recursive reference must not appear more than once`?
This is tested with PostgreSQL 9.6, but it's a general SQL question.
Is it possible to use self-joins of the recursive table in a recursive CTE (rCTE)?
I tried the following rCTE containing a self-...
- 3,692