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

SQL common record from same table

in source table, person can be part of n number of states. in target I need the common state for that pool. in below example I need CA and FL. Output:
vkase
  • 37
  • 1
  • 5
-2
votes
1 answer

How to get the current manager's name for each employee and add it as a new column in the same table?

I have a table Employees with emp_no and employees' info but there isn't a column specifying their manager at all. The names of the managers and their emp_no are in the Employees table with the rest of the staff. The managers' emp_no and dept_no are…
Г В
  • 1
  • 1
-2
votes
1 answer

top 100 users online since yesterday postgres

Here's another fun query i found to train SQL for product interviews. I am really doubtful about it tho - given the following table tab_a: user_id, date (they entered platform) find the top 100 users with the longest continuous streak of visiting…
Tytire Recubans
  • 967
  • 10
  • 27
-2
votes
3 answers

Ways to find Percentage increase in sales Month over Month

I want to find the percentage increase in Month over month sales amount using SQL Server. I want to find % MoM increase in sales by using self join and also using partition with rows unbounded preceding. I do not want to use lag(). Can anyone let me…
C.G
  • 21
  • 5
-2
votes
4 answers

Repeating rows based on the frequency

I have a table with 2 columns named A and B which is defined as : A B c1 2 c2 3 c3 4 The expected output is : A B c1 1 c1 2 c2 1 c2 2 c2 3 c3 1 c3 2 c3 3 c3 4
Arpit Tiwari
  • 55
  • 4
-2
votes
1 answer

How to fix errors in SQL Joins?

Courses Instructors Sections Students StudentSchedule StudentSchedule Part 2 Using the database provided, Write and Execute SELECT statements to get the following information: SIMPLE JOINS Sections Table How many total credit hours is instructor 6…
Marc
  • 1
  • 3
-2
votes
3 answers

SQL - self join 'n' times with condition

I have the following table: Id | Type -------------------------------------------- C1C1A90D-B131-4450-B1BF-5041F36F9144 | 1 C7B1752D-FD30-445A-AD6C-51D1434607D3 | 2 3AAF8BB6-A6D4-4780-BEF9-ACBBF75A85DE | …
user8666203
-2
votes
5 answers

Understanding the number of matched rows in LEFT JOIN

Here is my table structure: SQL Fiddle CREATE TABLE mytable ( id int, related int ); INSERT into mytable VALUES(1, NULL); INSERT into mytable VALUES(2, 1); INSERT into mytable VALUES(3, 1); And I have two queries: -- returns 3 rows SELECT…
Martin AJ
  • 6,261
  • 8
  • 53
  • 111
-2
votes
2 answers

Which query is more performant?

Suppose I have a self join query whose results are precisely the same for an inner join as they are for an outer join. In that case, is one more performant than another, or does it vary by what the query optimizer does? Typically I'd expect a LOJ…
jfalkson
  • 3,471
  • 4
  • 20
  • 25
-2
votes
1 answer

Oracle Query for joining on same column

How do I join on same column for different values and both the values should be and condition? eg: TABLEA.COLUMNA='XYZ' AND TABLEB.COLUMNA='PQR' Please note TableA and TableB are same table.
ketan
  • 1
-2
votes
1 answer

Self join tree MYSQL

Here is my categories table category_id | parent_id ____________________________ 27 | 25 28 | 27 29 | 25 26 | 28 25 | 0 30 | 0 31 | 26 …
Satanand Tiwari
  • 486
  • 4
  • 21
-2
votes
1 answer

Using self join on a table to compare two columns based on a linked column in the same table

I have the following: TableA ID | DocumentType | DocumentCode | DocumentDate | Warehouse | RefecenceCode ---+--------------+--------------+--------------+-----------+-------------- 1 | DeliveryNote | DOC-001 | 2017-04-21 | 1 | …
cdrrr
  • 1,138
  • 4
  • 13
  • 44
-2
votes
2 answers

MySQL self-join table error #1066 - Not unique table/alias: 'cities_translation'

I'm having a table for users and a table for orders and a table for cities names which is cities_translation. In users table I have user id, first name, lastname, city id and representative id. The representative is just another user in the same…
PHP User
  • 2,350
  • 6
  • 46
  • 87
-2
votes
3 answers

How to optimize this query with a self-join?

I have the following table: CREATE TABLE lab_data ( id int(11) NOT NULL, patient_sid int(11) DEFAULT NULL, double_value double DEFAULT NULL, string_value varchar(7) DEFAULT NULL, data_type_id int(11) DEFAULT NULL, event_date datetime…
horcle_buzz
  • 2,101
  • 3
  • 30
  • 59
-2
votes
1 answer

Mysql Self-join eludes me

I have haunted these forums always looking for tips on Joins and Sub_queries and have stockpiled solutions but I keep running into something that seems so simple but it eludes me. The past few days of searching this forum haven't produced my…
Thunder
  • 3
  • 1
1 2 3
…
84 85