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

Find user ID's who appear in a certain period and not in another

Given a table tbl_orders containing order details for many months, what's the most efficient SQL query to get a list of user ID's userID who appear in a given period BETWEEN 2019-01-01 AND 2019-01-31 but don't appear in another period BETWEEN…
Taher Elhouderi
  • 233
  • 2
  • 11
2
votes
1 answer

If a product was ordered earlier

Table : order_date. order_id. customer_id. product id 2020-01-01 O1. C1. P1 2020-01-01 O1. C1. P2 2020-01-01 O1. C1. P3 2020-01-10 O2. C1. …
Pratibha UR
  • 116
  • 7
2
votes
1 answer

How to self join with nulls postgresql

I want to show the names of the customers along with the names of those who referred them. I thought I was on the right track but the result is messed up. I tried conditions ReferredBy NOT NULL in WHERE clause, ReferredBy NOT NULL in ON clause - no…
eyei
  • 402
  • 4
  • 12
2
votes
1 answer

How to Mapping Columns in a Self-Join table!

I have a parent/child table and want to update Its PK and FK to new values. the problem is that oldParent Ids Should Sync with new ones matching with Old Ids. so: I have this data as a temp table: OldID | OldParentID | NewID | NewParentID 1 …
Mehdi
  • 5,435
  • 6
  • 37
  • 57
2
votes
1 answer

R data.table: optimize speed of row operations by (different) groups

So, I have this huge data set (possibly thousands of entries) that is received by my code as a data.table in long format, that looks like this: #sample DT sample_size = 8 DT0 <- data.table( DATE = seq.Date(from = as.Date("2020/3/01"), by = "day",…
anonymitySet
  • 23
  • 3
2
votes
1 answer

JOOQ how to self join

Now my code looks like this: EmployeeTable employee = EMPLOYEE.as("employee"); EmployeeTable boss = EMPLOYEE.as("boss"); Map result = dslContext.select(employee.fields()) .from(employee) .join(boss) …
First Sin
  • 94
  • 6
2
votes
1 answer

Efficient way to handle below query? in SQLServer or PostgreSql (Self join)

I have an employee table as below I need to select employee_id, manager_id, result. result should have true or false depends on below conditions. If the employee is manager for someone then true If the employee has a manager then true I came up…
Kavi
  • 140
  • 1
  • 9
2
votes
2 answers

sql server - Combine single ids into range

I have a table that contains lots of integers. This table gets queried and the results end up being turned into xml. If the table contains for example the following items: SELECT itemId FROM items WHERE enabled = true 1 2 3 5 The my final xml…
Kyle
  • 17,317
  • 32
  • 140
  • 246
2
votes
1 answer

Postgresql - poor self-join performance

my first question on SO, so apologies in advance! I have Postgresql (12.2) running on Windows Server 2016 and am experiencing poor performance with a simple self-join on 2 numeric columns. The table itself (form_content) is made up of 27 columns and…
amantadine
  • 21
  • 2
2
votes
1 answer

LINQ self join in ASP.NET MVC3

I have a situation where I need to do a self join on a table in LINQ. The table consists of fields ItemsID, Title, SeriesTitle, and many other. An item can be either a series or members and I can tell that by looking into ItemId which has "S" or "M"…
ACS
  • 443
  • 4
  • 15
2
votes
4 answers

Understanding Order of ON Clause in Self-Joins (SQL)

I am trying to understand the SQL self-join - especially how the order of the ON clause matters in the query. This is probably a basic question but please bear with me as I'm a beginner in query language. This is actually a LeetCode Question - #181…
PotentialLime
  • 81
  • 7
2
votes
4 answers

Counting events before a specific event

Let's say, I have a table with the following columns: date | event | user_id | unit_id |cost | ad_id | spend 03-15 | impression | 2353 | 3436 | 0.15 | NULL | NULL 03-15 | impression | 2353 | 3436 | 0.12 | NULL | NULL 03-15 |…
ulm
  • 49
  • 5
2
votes
2 answers

MySQL to return unique rows of members who have visited one page but not in another

The database stores the "email" and "page" of every "visit". I want to find the emails of the people who have visited the "checkout" page , possibly many times, but have NOT visited the "Thankyou" page. i.e abandoned carts; they got as far as…
Matt Walker-Wilson
  • 63
  • 6
2
votes
4 answers

SQL: Remove duplicates in self-join

I have the following table (called t1): | id | Name | | 1 | Charlie | | 2 | Bob | | 3 | Alice | I want to match the table with itself (self-join) but only choose a combination that has not already appeared. So far, I have the…
Cooler
  • 31
  • 4
2
votes
1 answer

Oracle SQL self join performance

lets say I have a table called order with following data. I need to get the customer_name along with the no. of orders they have placed. Table name: order id | customer_name | item 1 | Siddhant | TV 2 | Siddhant | Mobile 3 | Sankalp …
siddhant
  • 35
  • 6
1 2 3
…
84 85