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

self join query

Consider the following table: mysql> select * from phone_numbers; +-------------+------+-----------+ | number | type | person_id | +-------------+------+-----------+ | 17182225465 | home | 1 | | 19172225465 | cell | 1 | |…
MCS
  • 22,113
  • 20
  • 62
  • 76
4
votes
1 answer

Subsonic : Self Join , Table Alias

Is there a way with subsonic to preform a self join?
Matt
  • 41
  • 1
4
votes
1 answer

Pandas: Dataframe self-join with complex conditions

I have a DataFrame consisting of a set of data from 2 player games. Each game (with unique id) has a number of rounds where each of the 2 players chose some action. It looks like the following (I've removed some of the rows for clarity): gameId …
Andrew Mao
  • 35,740
  • 23
  • 143
  • 224
4
votes
4 answers

Transitive SQL query on same table

Hey. consider d following table and data... in_timestamp | out_timestamp | name | in_id | out_id | in_server | out_server | status timestamp1 | timestamp2 | data1 |id1 | id2 | others-server1 | my-server1 | success timestamp2 |…
MiKu
  • 385
  • 1
  • 5
  • 16
4
votes
1 answer

How to use join in my case Postgres query ?

i am trying to get product's name and categ_id. I have two tables that are, product_template categ_id name 7 Nokia classic 7 Nokia lumia 8 samsung s3 6 huawai this table have product that i want to…
user3664724
  • 425
  • 1
  • 6
  • 18
4
votes
1 answer

Doctrine2 Self Join, How to join on self without relationship column?

Example mysql query: SELECT a1.* FROM agreement a1 LEFT JOIN agreement a2 on a1.agreementType = a2.agreementType and a2.id > a1.id WHERE a2.id is null The purpose of the query is to get the last agreement of the type returned. There are many types…
user1828738
  • 181
  • 2
  • 7
4
votes
3 answers

Rails - Polymorphic Self-Join Model Associations

I'm working on building an app to keep track of product designs, and I'm having some trouble with my associations. Basically I have a model (Assembly) which needs to have polymorphic association, but also needs to be able to belong to itself. To…
rjblake1984
  • 41
  • 4
4
votes
2 answers

mysql join with the same table without duplicates

I have a table instructors with the following strcture/data: ----------------- |id | name | ----------------- |1 | saad | |2 | imran | ----------------- I want to do a join with the same table without returning duplicate results. If…
Saad Imran.
  • 4,480
  • 2
  • 23
  • 33
4
votes
2 answers

Adding counter cache to self join table in rails

I am trying to add a counter cache on a a column in a self join association. I have two models User and followings. User has followers and followees, who are from the user table itself. User.rb has_many :followings has_many :followers, :through…
abhilash
  • 1,587
  • 2
  • 10
  • 17
4
votes
3 answers

C# LINQ select items tricky filtering

I'm using C# 3.5 I have an IList, where MyItemLinkProperty represents the link between an item and it's properties (one or many). It has ItemId, PropertyId, PropertyValue. In this list ItemId can occur many times as an item can…
Martin
  • 83
  • 1
  • 7
4
votes
1 answer

Select Last Childs in a self-joined table in EF

I have a table like this : Id int Not Null PK Unique RequestNO int Not Null RefrenceId int Null //Self Join to Id If I have these records in this table : Id RequestNO RefrenceId …
Ali Foroughi
  • 4,540
  • 7
  • 42
  • 66
4
votes
1 answer

How do I do reflexive self-join relationships in ActiveRecord?

I'm trying to implement a social networking style friendship model and I didnt have much much luck trying to figure out the plugins available out there. I think I'll learn Rails better if I do it myself. So here's what I have : class User <…
udit
  • 2,745
  • 3
  • 33
  • 44
3
votes
2 answers

MySQL self-join with order by optimization

I have such a query: SELECT va.value, vc.value FROM votingapi_cache va LEFT JOIN votingapi_cache vc ON vc.content_id = va.content_id WHERE va.content_type = 'node' AND va.value_type = 'percent' AND va.tag = 'vote' AND va.function = 'average' AND…
breethe
  • 613
  • 1
  • 6
  • 18
3
votes
2 answers

self join in a table

CustID Name ReferredBy 1 Neeta Sayam 2 Dolly Dilly 1 3 Meena Kimi 2 Find the names of all customers who are referred by others. the output should be dolly dilly and meena kimi. I have…
coder25
  • 2,363
  • 12
  • 57
  • 104
3
votes
3 answers

How to write a SQL statement to return Distinct records based on X, Y, and Z columns and the first value in table for Column A

I am struggling to wrap my head around this. I have a data table like…
Hairy
  • 41
  • 5
1 2 3
…
84 85