Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [self-join]

The 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-...