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
1 answer

PHP MySQL Join Users Table to Multiple Columns & Rows

I've been having some issues joining tables in a project I'm working on, for projects. I have 3 tables, one for Projects, one for Customers, and one for Users. I'm trying to store the ID of the Customers and Users in the Projects table, and join…
cwaddilove
  • 67
  • 1
  • 9
3
votes
2 answers

Select Duplicate Entries from a database table

I have a two columns in a table say columnA and columnB. I want to select duplicate entries for columnA where columnB=xx or columnB=yy. For Example columnA columnB 12 abc 12 pqr 11 abc 10 pqr 9 xyz for above table I want…
Kalpesh Patel
  • 1,638
  • 1
  • 20
  • 35
3
votes
4 answers

Self join vs group by when counting duplicates

I'm trying to count duplicates based on a column of a table in an Oracle Database. This query using group by: select count(dockey), sum(total) from ( select doc1.xdockeyphx dockey, count(doc1.xdockeyphx) total from ecm_ocs.docmeta doc1 where…
carcaret
  • 3,238
  • 2
  • 19
  • 37
3
votes
4 answers

Understanding Self Join

I was practicing self join and here's a thing I do not understand in writing query. I have a table 'employee' The employee table contains three records. +-----+---------------+------------+ | id | employee | manager_id…
Miru
  • 158
  • 10
3
votes
2 answers

Do I need a loop or is their an easier way here

I have a table called employees that contains the columns. ID | Name | Salary | Department_id | Boss_id So all employees are listed in this no matter where they're bosses or not. If someone is not a boss then their value for Boss_id will be…
Lola Wormald
  • 35
  • 1
  • 5
3
votes
2 answers

How To Limit Self Join To Top 1 in SQL Server

I need to perform a self join that can result in multiple rows, but I need to limit the join to a single row per record. When multiple rows match the join criteria, only the value row with the maximum PK should be used. Here is a simplified…
kakridge
  • 2,153
  • 1
  • 17
  • 27
3
votes
3 answers

Link subsequent patient visits from same table in SQL

I have a table containing records for patient admissions to a group of hospitals. I would like to be able to link each record to the most recent previous record for each patient, if there is a previous record or return a null field if there is no…
Lauren
  • 45
  • 4
3
votes
1 answer

MongoDB SELF JOIN query having 1 collection

I'd like to do something like SELECT e1.sender FROM email as e1, email as e2 WHERE e1.sender = e2.receiver; but in MongoDB. I found many forums about JOIN, which can be implemented via MapReduce in MongoDB, but I don't understand how to do it in…
JustUser
  • 91
  • 1
  • 7
3
votes
2 answers

SQL Turn 1 field, multiple records into 1 record multiple fields

If I were more experienced, I'd be able to use similar "merge rows" questions to figure this out, but I can't get them to work. I've got about 2000 "garages" in an accdb with certain categories (actually clinics with patient data, but simplified…
Learn Long and Prosper
  • 31
  • 2
3
votes
1 answer

Django queries when Joining the same table on itself

I find at my job I often have to join the same table on itself, partly because I have no control of the DB design, and partly because things are wanted pretty custom most of the time. Select distinct t.label, z.dupCount From optevents t …
Sam Hammamy
  • 10,819
  • 10
  • 56
  • 94
3
votes
2 answers

Self join with inner and outer join query

I have a table that is set up so that one column (attribute) contains information like first name, last name, account number and any other information related to a thing in the database. Another column (attributeType) contains a number indicating…
Ban Atman
  • 177
  • 2
  • 3
  • 10
3
votes
2 answers

oracle sql self-join filter in the join clause vs where clause

Suppose I have a table Table A --------------- id date_id (yyyyMMdd format) --------------- 1 20120101 2 20120102 3 20120103 What is the difference between doing: select a1.* from A a1 left outer join A a2 on a1.id =…
fo_x86
  • 2,583
  • 1
  • 30
  • 41
3
votes
7 answers

For this scenario,does a single self-join exist?

I have a table ALPHA with 2 fields GroupId,Member: GroupId | Member; A1----------A; A1----------B; A1----------C; A2----------A; A2----------B; A3----------A; A3----------D; A3----------E; Objective: Given the input of - A,B,C - I have to query…
Jarvis Bot
  • 481
  • 6
  • 15
3
votes
3 answers

UPDATE rows with values from the same table

I have a table like this: +------+-------+ |ID | value | +------+-------+ | 1 | 150 | | 2 | | | 3 | | | 4 | | | 5 | 530 | | 6 | 950 | | 7 | 651 | +-------+------+ I want to copy the last 3 values…
Farhad Irani
  • 115
  • 1
  • 2
  • 6
3
votes
1 answer

Oracle Self-Join on multiple possible column matches - CONNECT BY?

I have a query requirement from ----. Trying to solve it with CONNECT BY, but can't seem to get the results I need. Table (simplified): create table CSS.USER_DESC ( USER_ID VARCHAR2(30) not null, NEW_USER_ID VARCHAR2(30), …
James King
  • 6,233
  • 5
  • 42
  • 63
1 2 3
…
84 85