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
143
votes
5 answers

What is SELF JOIN and when would you use it?

What is self join and when would you use it? I don't understand self joins so a layman explanation with an example would be great.
Imran
  • 11,350
  • 20
  • 68
  • 78
119
votes
7 answers

Simplest way to do a recursive self-join?

What is the simplest way of doing a recursive self-join in SQL Server? PersonID | Initials | ParentID 1 CJ NULL 2 EB 1 3 MB 1 4 SW 2 5 YT NULL 6 IS …
Chris
  • 3,081
  • 3
  • 32
  • 37
98
votes
14 answers

Explanation of self-joins

I don't understand the need for self-joins. Can someone please explain them to me? A simple example would be very helpful.
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062
37
votes
6 answers

INNER JOIN same table

I am trying to get some rows from the same table. It's a user table: user has user_id and user_parent_id. I need to get the user_id row and user_parent_id row. I have coded something like this: SELECT user.user_fname, user.user_lname FROM users as…
user1718343
  • 725
  • 2
  • 9
  • 18
25
votes
1 answer

combinations (not permutations) from cross join in sql

If I have a table that I'd like to cross join to itself, how can I remove the duplicate rows? Or to put it another way, how can I do a "order doesn't matter" cross join? So for example, if I have a table T: field | ------- A | B | C …
Ramy
  • 20,541
  • 41
  • 103
  • 153
21
votes
1 answer

Entity Framework 4.1 Code First Self-Referencing One-to-Many and Many-to-Many Associations

I have a User that can have collection of users he likes... Another user can have collection of users he likes.... If User A likes User B and if User B likes User A, then they get to hang out. I need to send each other their contact info. How do we…
Preetham Reddy
  • 611
  • 2
  • 8
  • 19
18
votes
1 answer

Can't understand EclipseLink warning

I'm using EclipseLink 2.3.1 to model self referencing table with JPA 2. I get weird warning from EclipseLink when I create the EntityManager. [EL Warning]: 2011-11-27 14:28:00.91--ServerSession(8573456)--Reverting the lazy setting on the OneToOne or…
gamliela
  • 3,447
  • 1
  • 35
  • 41
16
votes
1 answer

Rails: How do self-referential has_many models work?

So, I'm working on an app where I want to users to be able to group objects in "folders". Basically: User has_many :foos Foos don't have to be in a folder, but they can be. In that case: Folder has_many :foos and Foo belongs_to :folder Now, I'd like…
Andrew
  • 42,517
  • 51
  • 181
  • 281
16
votes
1 answer

Rails: self join scheme with has_and_belongs_to_many?

I would like to create a structure of Users having many friends, also of class User: class User < ActiveRecord::Base has_and_belongs_to_many :friends, class_name: "User" end I do not need any details of their relationship thus I do not use…
fakub
  • 327
  • 2
  • 13
16
votes
2 answers

First observation by group using self-join

I'm trying to get the top row by a group of three variables using a data.table. I have a working solution: col1 <- c(1,1,1,1,2,2,2,2,3,3,3,3) col2 <- c(2000,2000,2001,2001,2000,2000,2001,2001,2000,2000,2001,2001) col4 <-…
Brad
  • 813
  • 1
  • 10
  • 20
11
votes
3 answers

MySql. How to use Self Join

I need to use Self Join on this table. +------------+------+--------+ | Country | Rank | Year | +------------+------+--------+ |France | 55 | 2000 | +------------+------+--------+ |Canada | 30 | 2000 …
hank99
  • 185
  • 1
  • 2
  • 11
9
votes
1 answer

SQL: self join using each rows only once

Possible Duplicate: combinations (not permutations) from cross join in sql I've currently got a table with the following records: A1 A2 A3 B1 B2 C1 C2 Where the same letter denotes some criteria in common (e.g. a common value for the column…
EoghanM
  • 25,161
  • 23
  • 90
  • 123
9
votes
1 answer

SQL JOIN with the same table

I am trying to query in SQL and I can not resolve it. I have a table tCliente: What I want to do is a JOIN with the same table to find each pair of clients that lives in the same city. I try to do this: SELECT DISTINCT c.codiClien, c.nombreClien,…
Jonatan Lavado
  • 954
  • 2
  • 15
  • 26
9
votes
1 answer

recursive self query

I have the following table: myTable: +----+----------+ | id | parentID | +----+----------+ | 1 | null | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 4 | ----------------- i would like to get all rows tracing back until…
iddqd
  • 700
  • 7
  • 21
9
votes
2 answers

ActiveRecord: Nullify foreign key after deleting a child in self-joins

I have a self-join for my model in ActiveRecord, as follows: class Employee < ActiveRecord::Base has_many :subordinates, class_name: "Employee", foreign_key: "manager_id" belongs_to :manager, class_name:…
Neel Vasa
  • 169
  • 1
  • 8
1
2 3
…
84 85