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

SQL create an array from several rows

I have a table with a one to many mapping from ID to some_value. What query can give me all of some_value for a particular ID? I can use any array, string, or other object useful in SQL as the final result in list_of_values. You don't need to use…
Don P
  • 60,113
  • 114
  • 300
  • 432
3
votes
1 answer

Postgres/JSON - translate parent child relation to a json

I have the following records in a postgres database. The parent_pk is related to the pk in a parent-child relation. pk name type parent_pk --- ---- ---- --------- 1 …
blue01
  • 2,035
  • 2
  • 23
  • 38
3
votes
2 answers

Multiple Foreign keys to a single table and single key pointing to more than one table

I need some suggestions from the database design experts here. I have around six foreign keys into a single table (defect) which all point to primary key in user table. It is like: defect (.....,assigned_to,created_by,updated_by,closed_by...) If I…
akhil_mittal
  • 23,309
  • 7
  • 96
  • 95
2
votes
4 answers

SQL Server - JOIN in UPDATE statement

I have a temp table with a number of non distinct customer records, and I want to update the same column in all rows for the same email address, if they have an existing value: CustomerID | Email | Pref1 | Pref2 |…
TheTor
  • 53
  • 1
  • 11
2
votes
4 answers

MySQL how to join on same table including missing rows

I have a table with text in various language. Its defined like this: Id|Language|Text EXAMPLE DATA 0, ENU, a 0, DAN, b 1, ENU, c 2, ENU, d 2, DAN, e 3, ESP, f 3, ENU, g Language and Id form the key. Now I want to extract all texts in a langauge…
Muleskinner
  • 14,150
  • 19
  • 58
  • 79
2
votes
3 answers

Join table on itself - performance

I would like some help with the following join. I have one table (with about 20 million rows) that consists of: MemberId (Primary Key) | Id (Primary Key) | TransactionDate | Balance I would like to get the latest Balance for all the customers in one…
PKK
  • 127
  • 4
  • 15
2
votes
1 answer

How to do an upper recursive self-join in SQL Server?

How can I do a recursive self-join in SQL Server ? I have a table like this: TableID | ParentID 1 | NULL 2 | 1 3 | 1 4 | 3 5 | NULL 6 | 4 7 | 6 I want to get the…
LFB
  • 175
  • 2
  • 2
  • 12
2
votes
1 answer

Can't understand the mysql self left-join query

I come across a query which is, SELECT it1.survey_set_id, it1.type, it1.value FROM survey_condition_filter it1 LEFT JOIN survey_condition_filter it2 ON(it1.survey_set_id = it2.survey_set_id AND it2.type = 3002) WHERE it1.type IN (2000, 2001, 2002)…
Abhijith Ea
  • 115
  • 11
2
votes
2 answers

How to use Group By and self-join to return min, max, open, and close daily price restult set?

SOLVED All hail StackOverlow! While I was gone, people left 2 solutions (thanks guys--what is the protocol for handing out karma for two working solutions?) Here is the solution that I came back to post. it is derived from yet ANOTHER StackOver…
shamelesshacker
  • 185
  • 1
  • 7
  • 18
2
votes
1 answer

How to join tables of events to report non-events?

Employees must complete continuing education modules to stay competent. I need to build a report showing supervisors which modules their employees have – and have not -- completed. I have a simple table of employees and their supervisors. Each row…
user18612109
  • 25
  • 5
2
votes
2 answers

Find purchase if same item on different days

I'm trying to find customers that bought the same item more than once in different days. I got it partially working. I can't get the customer first/last name and item_name without adding it to the group by clause. In addition, I want to include a…
Beefstu
  • 804
  • 6
  • 11
2
votes
0 answers

Flask SQL Alchemy self join

I want to perform self join with the use of Flask SQL Alchemy. I have following table in PostgreSQL: CREATE TABLE table0 ( id serial PRIMARY KEY, property_0 TEXT, property_1 TEXT); insert into table0(property_0, property_1)…
Zekhire
  • 115
  • 1
  • 2
  • 8
2
votes
0 answers

Python Pandas DataFrame Self-Join Table

Using Pandas DataFrames, I am trying to implement self join on a data table with shop ids, product ids, and levels. The Shop and Product ID determine a level. Unknown levels are indicated by null. The problem is as follows: For Shop ID A, find the…
Rob R
  • 21
  • 2
2
votes
2 answers

SQL Self Join to create Header and Line level columns

I've a table with the following data. Item number Date Reference DocNumber Qty Site PLU-1000 4-JAN Header JRN - 523 2 SFT RIN-000138 4-JAN Line JRN - 523 2 SFT RIN-000096 4-JAN Line JRN - 523 6 SFT RIN-000247 4-JAN Line JRN -…
Umer Shamshad
  • 47
  • 3
2
votes
1 answer

Prisma many to many self relation error due to @relation and generated type input

Using Prisma 3.7.0. I'm following this example in the docs model Person { id Int @id @default(autoincrement()) name String? followers Follows[] @relation("follower") following Follows[] @relation("following") } model…
shmuels
  • 1,039
  • 1
  • 9
  • 22
1 2 3
…
84 85