Questions tagged [self-join]
The self-join tag has no usage guidance.
28
questions
0
votes
1answer
14 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
1answer
16 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 | ...
0
votes
0answers
12 views
I'm trying to order a table with 3 columns as if the columns were merged into 1 such that it would sort as 3 separate entries
I've hit a somewhat unique obstacle with my current project. Below is a mock MySQL table to better describe the issue I'm trying to solve.
mysql> describe `minimized_event_instructions`;
+---------...
1
vote
1answer
25 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'
...
1
vote
2answers
41 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'.
0
votes
1answer
35 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 ...
4
votes
1answer
52 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 ...
0
votes
0answers
16 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 ...
0
votes
3answers
110 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
1answer
60 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 ...
0
votes
0answers
145 views
PostgreSQL: join by dynamic nested JSONB attribute
I'm having quite an unusual task and I'm not even sure if it's solvable.
I have a table with JSONB column which can contain arbitrary nested JSON objects. Let's call it objects table.
And I have a ...
6
votes
1answer
227 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
votes
2answers
116 views
Can a transitive join loop infinitely?
I have a table that stores alternative product:
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------...
1
vote
2answers
184 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 ...
1
vote
1answer
1k 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....
-3
votes
1answer
241 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 ...
0
votes
1answer
33 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 ...
0
votes
1answer
539 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 ...
0
votes
1answer
65 views
nontrivial joining a relation with itself
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
vote
0answers
182 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 ...
2
votes
1answer
35 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 ...
1
vote
3answers
306 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 ...
1
vote
2answers
605 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
---------...
4
votes
1answer
730 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 ...
1
vote
2answers
3k 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 ...
3
votes
1answer
1k 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 ...
0
votes
1answer
65 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 ...
3
votes
2answers
631 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-...
