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

Select Two query in one query with multiple column

I am getting the issue I want to select two different queries in one query but different column Note: Not like union because union put these two queries in one column select count(id) as lead,SUM(ol.publisher_earned) as earning from offer_process as…
Habib Ur Rehman
  • 59
  • 9
3
votes
1 answer

Efficient indexing / joining in data.table across multiple dependent conditions for stop detection algorithm

Edit: Real data set available here With thanks to Wang, Rui, Fanglin Chen, Zhenyu Chen, Tianxing Li, Gabriella Harari, Stefanie Tignor, Xia Zhou, Dror Ben-Zeev, and Andrew T. Campbell. "StudentLife: Assessing Mental Health, Academic Performance and…
Danielle McCool
  • 146
  • 2
  • 11
3
votes
4 answers

self join after an inner join

I am finding what cities have the same name in different states. The city name and state name are in seperate tables (cities and states) and can be inner joined over a seperate common column. select c1.city, c1.state, c2.city, c2.state from cities…
shitathakin
  • 77
  • 6
3
votes
2 answers

JPA Criteria Builder query with inner SELECT statement

I need to create the below SQL join condition using JPA criteria builder, SELECT * FROM student s1 INNER JOIN (SELECT subject,teacher,MIN(marks) AS marks FROM student GROUP BY subject, teacher) s2 ON s1.subject = s2.subject AND s1.teacher =…
VINAY S G
  • 505
  • 3
  • 8
  • 16
3
votes
3 answers

Aggregating data with a self-joined table in SQL Server 2008r2

I'm trying to provide reporting functionality on a typical restaurant type database. I describe the specifics of the problem below, but in a nutshell I need to be able retrieve aggregate data (sums and counts) for items that relate to a heirarchical…
tobyb
  • 696
  • 9
  • 18
3
votes
3 answers

Oracle SQL looped self-join

Context: Let's say that I have table that has a FOREIGN KEY which references its own PRIMARY KEY, like this: |---------------------|------------------|------------------| | ID | NAME | PARENT_ID …
michal.d
  • 41
  • 3
3
votes
5 answers

SQL join table to itself to get data for previous year

SQL. How can I join table to itself to get desired results just as shown in the table below. The logic is that I want to have Units for the same product and corresponding month of previous year. The simple left join on source table to itself on key…
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
3
votes
4 answers

Mysql Matching "Same" Emails

I have a table with 2 columns email and id. I need to find emails that are closely related. For example: john.smith12@example.com and john.smith12@some.subdomains.example.com These should be considered the same because the username (john.smith12)…
user3783243
  • 5,368
  • 5
  • 22
  • 41
3
votes
1 answer

LINQ: Self join query, how to accomplish this?

Can anyone help? I have 1 class, basically it holds Members and within that class is a List. The members i have in a List also... So basically it goes like this, I have 2 members and each member has a number of sessions. I wish to only return each…
Martin
  • 23,844
  • 55
  • 201
  • 327
3
votes
2 answers

ActiveRecord::AssociationTypeMismatch in Controller#create on dropdown select for a Rails self join

I'm getting an ActiveRecord::AssociationTypeMismatch error on my self join in Rails 5 that I can't figure out how to fix. It's a simple rails app where a user can share a quote by an Artist (such as David Bowie) about another Artist (such as Lou…
Lee McAlilly
  • 9,084
  • 12
  • 60
  • 94
3
votes
1 answer

Hoping to remove an ugly self-join on a CTE

I have a query that creates a sorted dictionary (sorted in that there is an incremental id to identify relative position of the keys). I then wish to know, for each row, if the value exists as a key in any other row later on in the dictionary. I'm…
MatBailie
  • 83,401
  • 18
  • 103
  • 137
3
votes
2 answers

How to map child parent on a single table using self Join in SQL Server?

I have the following table with child parent relationship. ID Title PageID IsParent ParentID IsActive 1 Dashboard 2125 True NULL True 2 Site Analytics 22 False NULL …
Albert Einstein
  • 7,472
  • 8
  • 36
  • 71
3
votes
2 answers

Can a self join association be done allowing the reference to be blank?

My app has a Waiter model to manage a list of people who are on a waiting list. Each of those 'waiters' can refer others so they can join the list. Also, I want to track down who's referred a particular waiter. I have defined a self join…
alopez02
  • 1,524
  • 2
  • 17
  • 36
3
votes
1 answer

Self joining a table n times

I'm trying to find a biological "find core genome of a given set of organisms" problem. Essentially, given a list of organisms, find all genes that are common to them. To abstract away biology, you can think of find all colours that are favourite…
player87
  • 1,781
  • 1
  • 14
  • 21
3
votes
2 answers

MySQL where subselect on multiple fields

I'm trying to do a single MySQL query to select data inside a third party function, the problem I have however they only pass me the ID. I need to use that ID to select all the related records. Currently the MySQL statement looks something like…
trevrobwhite
  • 443
  • 1
  • 7
  • 22
1 2 3
…
84 85