Questions tagged [self-join]

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