Questions tagged [self-join]
The self-join tag has no usage guidance.
0
votes
2answers
50 views
Can a transitive join loop infinitely?
I have a table that stores alternative product:
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------...
1
vote
2answers
43 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
37 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 with avoiding duplicate pairs like so:
-- SELECT a.*, b.* FROM my_table a JOIN my_table b ...
-3
votes
1answer
48 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
19 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
44 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
40 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
79 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
21 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
111 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
299 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
---------...
3
votes
1answer
107 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
1k 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 ...
2
votes
2answers
471 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
59 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 ...
2
votes
1answer
276 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-...
