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
3
votes
2 answers

LEFT JOIN needed? WHERE conditions behave similarly to INNER JOIN

Trying to do a simple division of counts based on different criterion from the sample table, grouped by movie rating. I am using the sample SAKILA schema that comes by default with MySQL. You can see the separate counts I'm expecting to see from…
Sidney Carton
  • 175
  • 1
  • 1
  • 11
3
votes
4 answers

SQL - Compare rows in a table to find column differences - self join

I have the following table: DECLARE @TABLE_A TABLE ( id int identity, name varchar(20), start_date datetime, end_date datetime, details nvarchar(500), copied_from int) Users can clone a row and re-insert it into the same…
03Usr
  • 3,335
  • 6
  • 37
  • 63
3
votes
2 answers

Joining a table on itself with aggregation

I am trying to (efficiently) fetch rows from the connections table, where the startdate is the latest within cpid - for selected cpid's. Here's an example of the data in the connections table with rows I want marked with <<< connid cpid …
Rob
  • 301
  • 3
  • 10
3
votes
2 answers

Self-join on a non-key column

Are there are analytical consequences on JOINing a table to itself ON a column that does not hold unique values? Most of the self join queries I have seen are done on a PK column. I have run into problems getting my numbers right so I am wondering…
disasterkid
  • 6,948
  • 25
  • 94
  • 179
3
votes
1 answer

Print parent hierarchy from same table in mysql database

I have a table category which has fields and values as shown below in MYSQL database. id name parent sort_order 1 Men null 0 2 Women null 1 3 shirt 1 0 4 salwar 2 1 Here parent is a foreign key…
Vipin CP
  • 3,642
  • 3
  • 33
  • 55
3
votes
3 answers

MySQL approach: Large self-joins to set values?

I'm working with a 12-million record MyISAM table with surname, address, gender and birthdate fields: ID SURNAME GENDER BDATE COUNTY ADDRESS CITY 1 JONES M 1954-11-04 015 51 OAK ST SPRINGFIELD 2 …
Dan
  • 31
  • 1
3
votes
2 answers

How to find multiple records(attendance) of same date with alias of same table in MySql

I have a table named rjs_attendance with following four column _________________________________________________ |attenedance_id | admin_id | note | created_date| ------------------------------------------------- A user can make attendance several…
Ahmad Asjad
  • 825
  • 1
  • 8
  • 29
3
votes
3 answers

Self join vs. inner join

What is the difference between self join and inner join?
Nilesh Kumar
  • 111
  • 1
  • 1
  • 6
3
votes
2 answers

Need some serious help with self join issue

Well as you may know, you cannot index a view with a self join. Well actually even two joins of the same table, even if it's not technically a self join. A couple of guys from microsoft came up with a work around. But it's so complicated I don't…
kralco626
  • 8,456
  • 38
  • 112
  • 169
3
votes
2 answers

Retrieve value from column depending on another column

From this table of football players, how would you select the players' names alongside their captains' names? PlayerID | PlayerName | TeamCaptainID ===================================== 1 | Jay | 5 2 | John | 3 3 |…
Mihai Nagy
  • 177
  • 1
  • 8
3
votes
1 answer

Improving performance with a Similarity Postgres fuzzy self join query

I am trying to run a query that joins a table against itself and does fuzzy string comparison (using trigram comparisons) to find possible company name matches. My goal is to return records where the trigram similarity of one record's company name…
SBaha
  • 73
  • 2
  • 7
3
votes
0 answers

Recursively find all parents and children in self-joined table

I have a table called OrganisationTrees which self joins. Parents are called parent_tree; children are called sub_trees. I need to input a single OrganisationTree and then find all of its sub_trees and then all of its sub_trees etc. until all…
user3895379
  • 41
  • 5
3
votes
1 answer

Self join query: how to return children and parent row?

I have this table: Content: - id - parent_id - slug - creation_date parent_id is a foreign key pointing to the same table (content.id). I want to select all the rows in the table that are children of a parent_id in the same table. I would like the…
SrgHartman
  • 651
  • 2
  • 8
  • 23
3
votes
3 answers

How to get matching data from another SQL table for two different columns: Inner Join and/or Union?

I've got two tables in MS Access that keep track of class facilitators and the classes they facilitate. The two tables are structured as follows: tbl_facilitators facilID -> a unique autonumber to keep track of individual teachers facilLname -> the…
Levi Tonet
  • 33
  • 6
3
votes
1 answer

Faster subset aggregation in data.table

I want to add a new column to my data.table. This column should contain the min of two other columns of all rows that satisfy a certain condition. An example with a data.table looking like this: library(data.table) DT <- data.table(pattern=c("A", "A…
Roy van der Valk
  • 527
  • 1
  • 6
  • 18
1 2 3
…
84 85