Questions tagged [self-join]
The self-join tag has no usage guidance.
0
votes
0answers
13 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
40 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
54 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
27 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
190 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....
0
votes
2answers
63 views
Can a transitive join loop infinitely?
I have a table that stores alternative product:
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------...
1
vote
2answers
90 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
119 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
100 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
31 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
114 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
60 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
120 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
32 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
219 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
465 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
330 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
2k 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
2answers
768 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
62 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
427 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-...
