Stack Overflow
Stack Overflow

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
8
votes
4 answers

Many-to-many association with multiple self-joins in ActiveRecord

I am trying to implement multiple relations between records of the same model via self-joins (based on @Shtééf's answer). I have the following models create_table :relations, force: true do |t| t.references :employee_a t.string :rel_type …
Andrei
  • 10,918
  • 12
  • 76
  • 110
8
votes
6 answers

SQL self join pairwise

Suppose I have a table consisting of entries like ID Arrival Date Arrival City Departure Date Departure City 1 Jun 27 2015 Berlin Jun 20 2015 Paris 1 Jul 1 2015 Rome Jun 29 2015 Berlin 1 …
user787267
  • 2,550
  • 1
  • 23
  • 32
8
votes
4 answers

Comparing SQL Table to itself (Self-join)

I'm trying to find duplicate rows based on mixed columns. This is an example of what I have: CREATE TABLE Test ( id INT PRIMARY KEY, test1 varchar(124), test2 varchar(124) ) INSERT INTO TEST ( id, test1, test2 ) VALUES ( 1, 'A', 'B'…
Kyle
  • 17,317
  • 32
  • 140
  • 246
7
votes
1 answer

how do I add a foreign key pointing to the same table using phpMyAdmin?

I have an existing InnoDB table which already has foreign keys pointing to different tables. But when I try to create a foreign key pointing to the Primary index, I get an error (check data type). The table is User with User_Id as the Primary. I…
AFG
  • 1,675
  • 3
  • 22
  • 23
7
votes
1 answer

Odd behaviour of data.table's update on non-equi self-join

While preparing an answer to the question dplyr or data.table to calculate time series aggregations in R I noticed that I do get different results depending on whether the table is updated in place or returned as a new object. Also, I do get…
Uwe
  • 41,420
  • 11
  • 90
  • 134
7
votes
7 answers

How can I Ascertain the structure for each person from a self referencing table

I have the following tables: Employees ------------- ClockNo int CostCentre varchar Department int and Departments ------------- DepartmentCode int CostCentreCode varchar Parent int Departments can have other departments as…
Matt Wilko
  • 26,994
  • 10
  • 93
  • 143
7
votes
1 answer

Retrieving a row number from within a specific group of rows using a self join in JPA

Given a table named rating in a MySQL database. +-------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra …
Tiny
  • 27,221
  • 105
  • 339
  • 599
7
votes
1 answer

(self) join by time intervals

I have a table in an oracle database. The schema is create table PERIODS ( ID NUMBER, STARTTIME TIMESTAMP, ENDTIME TIMESTAMP, TYPE VARCHAR2(100) ) I have two different TYPE's: TYPEA and TYPEB. The have independent start and end times…
Erik
  • 2,137
  • 3
  • 25
  • 42
6
votes
3 answers

How to left join or inner join a table itself

I have this data in a table, for instance, id name parent parent_id 1 add self 100 2 manage null 100 3 add 10 200 4 manage null 200 5 add …
Run
  • 54,938
  • 169
  • 450
  • 748
6
votes
1 answer

Join two tables (with a 1-M relationship) where the second table needs to be 'flattened' into one row

Given the following tables: Student +----+-------+ | id | Name | +----+-------+ | 1 | Chris | | 2 | Joe | | 3 | Jack | +----+-------+ Enrollment +---------------+------------+-----------+----------+ | enrollment_id | student_id | course_id |…
Chris
  • 54,599
  • 30
  • 149
  • 186
6
votes
0 answers

Grails Self Referencing Criteria

In the project I´m working there is a part of the database that is like the following diagram The domain classes have a definition similar to the following: class File{ String name } class Document{ File file } class LogEntry{ …
Omar Yafer
  • 823
  • 6
  • 17
6
votes
4 answers

How do I Compare columns of records from the same table?

Here is my testing table data: Testing ID Name Payment_Date Fee Amt 1 BankA 2016-04-01 100 20000 2 BankB 2016-04-02 200 10000 3 BankA …
Chhun Panharath
  • 136
  • 1
  • 10
6
votes
2 answers

Self-join on a table with ActiveRecord

I have an ActiveRecord called Name which contains names in various Languages. class Name < ActiveRecord::Base belongs_to :language class Language < ActiveRecord::Base has_many :names Finding names in one language is easy…
lambshaanxy
  • 22,552
  • 10
  • 68
  • 92
6
votes
2 answers

In Yii2, how do I join a table to itself?

I have a page table. Pages can have parents, which are also pages. I want to select all pages with parent_id = NULL and their 'children'. But when I try this public function getPages() { return $this->hasMany(Page::className(), ['parent_id' =>…
Alec Smythe
  • 770
  • 7
  • 12
6
votes
1 answer

Self Join in Eloquent

How would you write a self join in eloquent? Would I need to define the relationship on the model? Here's my statement: SELECT t2.title FROM products t1, products t2 WHERE t1.id = $id AND t2.color_id = t1.color_id AND t2.id != $id
panthro
  • 22,779
  • 66
  • 183
  • 324
1
2
3
…
84 85