Questions tagged [self-join]

A table can be joined to itself and this is called a self-join. You can use a self-join to create a result set that joins records in a table with other records in the same table for example to filter a specific subset of records from within a single table without returning duplicate or similar rows.

Self-joins are useful in queries or subqueries such as the following:

  • a limited result set of rows is needed
  • a relationship between rows is needed
  • a calculation among rows is needed

Self-joins are also useful when converting vendor specific syntax to vendor agnostic syntax

References

1264 questions
6
votes
3 answers

Financial Data - R data.table - group by condiction

Given the following data.table with financial data: userId systemBankId accountId valueDate quantity description 871 0065 6422 2013-02-28 -52400 AMORTIZACION PRESTAMO 871 0065 6422 …
5
votes
1 answer

Rewrite self join to JPQL

I need to convert this self join to JPQL: SELECT s1.* FROM site AS s1 JOIN (SELECT site_type, MAX(last_update_date) AS LastUpdate FROM site WHERE site.last_update_date > "2011-02-27 16:57:53" GROUP BY site_type) AS s2 ON…
BigJ
  • 1,990
  • 2
  • 29
  • 47
5
votes
7 answers

How to remove duplicate rows in SQL Server self join

I am trying to modify one column value on the basis of another row in same table, so I am using self join, I am getting the expect changes, but its selecting both rows (original and modified one), I don't want to select original row, if any of its…
Ashok Damani
  • 3,896
  • 4
  • 30
  • 48
5
votes
3 answers

"recursive" self join in data.table

I have a component list made of 3 columns: product, component and quantity of component used: a <- structure(list(prodName = c("prod1", "prod1", "prod2", "prod3", "prod3", "int1", "int1", "int2", "int2"), component = c("a", "int1", "b", "b",…
PavoDive
  • 6,322
  • 2
  • 29
  • 55
5
votes
6 answers

Get Common Rows Within The Same Table

I've had a bit of a search, but didn't find anything quite like what I'm trying to achieve. Basically, I'm trying to find a similarity between two users' voting habits. I have a table storing each individual vote made, which stores: voteID itemID …
James B
  • 8,183
  • 4
  • 33
  • 40
5
votes
2 answers

Self-referencing models in Rails 3

I have an Entity model and I want to display connections between the Entities. ie, Entity 1 is connected to Entity 2. My thinking, right now, is to create a join model between the two called Connection and have it work like a traditional rails join…
Slick23
  • 5,827
  • 10
  • 41
  • 72
5
votes
4 answers

SQL query group by and having all

I have a table: Parent Child Educated 'P1', 'C1', 'YES' 'P1', 'C2', 'YES' 'P1', 'C3', 'NO' 'P2', 'C11', 'YES' 'P2', 'C12', 'NO' 'P3', 'C21', 'YES' 'P3', 'C22', 'YES' 'P4', 'C31', 'NO' 'P4', 'C32', 'NO' Now, I need to find all the parents who have…
Xavier DSouza
  • 2,861
  • 7
  • 29
  • 40
5
votes
1 answer

MYSQL: Avoiding cartesian product of repeating records when self-joining

There are two tables: table A and table B. They have the same columns and the data is practically identical. They both have auto-incremented IDs, the only difference between the two is that they have different IDs for the same records. Among the…
Silvio Donnini
  • 3,233
  • 2
  • 28
  • 29
5
votes
2 answers

Self join to a table

I have a table like Employee ================== name salary ================== a 10000 b 20000 c 5000 d 40000 i want to get all the employee whose salary is greater than A's salary. I don't want to use any nested or…
Mohit Vashistha
  • 1,824
  • 3
  • 22
  • 49
5
votes
0 answers

MonetDB- Why doubling self-joins can cause query execution time to go from milliseconds to hours on the same data?

I'm running SQL queries on pretty simple table. Those tables model the RDF graph using the 'vertical partition' introduced here. Thus, they all have the following model where s is the 'subject' and o is the object. CREATE TABLE a_table (BIGINT s,…
Fopa Léon Constantin
  • 11,863
  • 8
  • 48
  • 82
5
votes
6 answers

What are appropriate ways to represent relationships between people in a database table?

I've got a table of people - an ID primary key and a name. In my application, people can have 0 or more real-world relationships with other people, so Jack might "work for" Jane and Tom might "replace" Tony and Bob might "be an employee of" Rob and…
Emilio
  • 1,951
  • 3
  • 18
  • 24
5
votes
4 answers

#TAble is ambiguous

I am getting an error that states #Plans is ambiguous. This happens when I am joining a table to itself on a self join and am not sure why. Here is the code that leads to the error: Alter Table #Plans Add SecondPlanDate date Update #Plans Set …
SASUSMC
  • 681
  • 4
  • 20
  • 38
5
votes
1 answer

SQLAlchemy alias mixup

I have a problem with SQLAlchemy and aliases. I'm trying to do a self-join on a table called Task but SQLAlchemy confuses my aliased table with the non-aliased. My query is much more complex but this is a simplified version: baseTask =…
5
votes
2 answers

Mysql Self Join to find a parent child relationship in the same table

Im trying to calculate the amount of money won by all the offspring of a male race horse (Sire) over a time period. Listed by the Sire with the most amount of money won. I run the query and get the result Im after with one problem, I cant display…
stemie
  • 779
  • 3
  • 12
  • 27
5
votes
3 answers

Simple Self Join Query Bad Performance

Could anyone advice on how do I improve the performance of the following query. Note, the problem seems to be caused by where clause. Data (table contains a huge set of rows - 500K+, the set of parameters it's called with assums the return of 2-5K…
user1514042
  • 1,899
  • 7
  • 31
  • 57
1 2
3
84 85