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

How to display self-joined tree hierarchy table by using BigQuery?

I am working on the tree hierarchy of supervisors and their supervised employees. The difficulty is that some supervisors are employees supervised by other supervisors, and there are lots of it. For SQL queries I acquired from class, only about…
Sandy
  • 359
  • 4
  • 14
2
votes
1 answer

How to implement self join in JPA?

I have a table as follow: create table ServiceType ( typeCode varchar(32), parentTypeCode varchar(32), description varchar(255), primary_key(typeCode)) I try to implement an entity for this table , the code snippet just like: @Entity …
user714887
  • 21
  • 1
  • 1
  • 2
2
votes
4 answers

SQL join to identify group members

I have got a client table which pretty much looks like below: Client List customer no. Customer name 123 Kristen Smith 128 Jeremy Church 127 Alan Li 132 Ryan Nelson I need to…
Keen_Learner
  • 23
  • 3
2
votes
1 answer

How can i tell Hibernate to ignore "0" in a selfjoin @OneToOne

I'm writing a new application which uses a old Database. I have a Table named "score" looking like this: +-----+------------+--------------------------+ | id | name | sub_score …
Mintri
  • 133
  • 5
2
votes
6 answers

Convert Subquery to Self Join

New to SQL and I understand joins tend to be faster than subqueries. I have the following table, and my current query gives me the results I require, but I can’t wrap my head around a similar query that uses self join instead, assuming it’s…
Bink
  • 1,934
  • 1
  • 25
  • 41
2
votes
0 answers

Find fuzzy duplicates on single column rows with pg_trgm

I was trying to find duplicates on column rows, but as they are fuzzy (not the same value, misspelling, indent space) I have to use pg_trgm extensions and similarity() function to find those. The problem is: this query is quite long and inefficent,…
Pavel Nasevich
  • 400
  • 1
  • 3
  • 13
2
votes
1 answer

How to calculate a column with values dependent on previous calculations using Joins dynamically?

Current table looks like this: Each object has a unique name and it's parent name and a hierarchy. name parent hierarchy value A A 1 100 B A 2 0.5 C B 3 0.5 My expected result is: name parent…
Maadesh Sivakumar
  • 367
  • 5
  • 16
2
votes
1 answer

Filter Data based on values of single column

I have a table with following data. Now I only want to select GRP_ID whose ITEM_NO are only of Parcel Type. In case there are items which have TTYPE both as Parcel and Truck, then that should not come in result. GRP_ID ITEM_NO TTYPE 00001 12345 …
Vipin Vij
  • 23
  • 3
2
votes
2 answers

Duplicates in Pandas Dataframe after Self Merge

import pandas as pd I have a Dataframe Table d1 = pd.DataFrame({'ID_A':[1, 2, 3], 'name':['Micha', 'Micha', 'Lea']}) ----------------- - ID_A - name ----------------- - 1 - Micha - 2 - Micha - 3 - Lea ----------------- I…
steff9488
  • 139
  • 1
  • 9
2
votes
1 answer

MySQL join to same table

I've got one table (called staging) with the following relevant fields: id (PRIMARY) bundle_id (INT) product (enum H,L,D) bundle_code (enum 10,20) I need to search for a bundle_id where the bundle_code is 10, then also retrieve any other records…
Toby Nieboer
  • 86
  • 4
  • 11
2
votes
1 answer

Simulate ROW_NUMBER, RANK and DENSE_RANK with subquery

I wanted to use DENSE_RANK() function on one of my JasperReport query but it's not available for Firebird 2.5. But there is an example on the firebirdfaq.org site to simulate this function using aggregate functions and context variables or…
AlphaTry
  • 475
  • 5
  • 27
2
votes
1 answer

Self join data varying SQL Server

I have a table Employee which contains the name along with manager id so I am using a self join to find the manager of the employee. My table structure and table data: CREATE TABLE Employee ( EmployeeID INT PRIMARY KEY, Name NVARCHAR(50), …
user9461718
2
votes
1 answer

sql combine 2 rows into one

I have the following dataset: I am trying to convert the table on the left into the table on the right. I have several duplicates of orders with the same name but different products sold. I would like to combine the rows so it shows just one…
user7146708
  • 187
  • 4
  • 12
2
votes
4 answers

MSSQL get rows with timedifference below X

I have a table with an ID, a CHAR and a DATETIME field. Now I want to get all rows, which are having a DATEDIFF of 5 minutes or less. Sample data for reference: ID2 CHA Timer 1 B 2018-03-06 11:31:39 2 S …
XtremeBaumer
  • 6,275
  • 3
  • 19
  • 65
2
votes
2 answers

Need data from multple rows on single output row. Subquery?

I'm fairly new with working with MySQL so please excuse the vague title, I wasn't sure how to ask this question or if it truly does require a subquery. Edit: Thanks @O. Jones for revising the title. I have a table of employees, which is structured…
B.Vee
  • 45
  • 6
1 2 3
…
84 85