Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [self-join]

50 questions
11
votes
2 answers

self-joins in recursive CTE: `recursive reference must not appear more than once`?

This is tested with PostgreSQL 9.6, but it's a general SQL question. Is it possible to use self-joins of the recursive table in a recursive CTE (rCTE)? I tried the following rCTE containing a self-join of the recursive term x, WITH RECURSIVE x (id)…
tinlyx
  • 3,810
  • 14
  • 50
  • 79
7
votes
1 answer

Recursivly get a Tree Via self joined table

Using other questions here and Postgresql documentation I've managed to build a many-to-many self joined table. However adding a WHERE clause is giving me trouble. Problem: A Category can have many child categories, and many parent categories. Given…
dustytrash
  • 171
  • 5
6
votes
1 answer

Self-join example review in Books Online

Here in this example it states that performs a self-join, but it joins Sales.SalesPerson to Sales.SalesTerritory. I knew that self join means joining the same table to itself but here aren't Sales.SalesPerson and Sales.SalesTerritory different…
igelr
  • 2,162
  • 3
  • 26
  • 56
5
votes
1 answer

Is it possible to upsert into a self-referencing table with a single statement in PostgreSQL?

If I have a table A, like so: A { id SERIAL title TEXT ... parentId INT references A.id via a foreign key constraint } I am pulling data from a source table - A_SOURCE - where there isn't a parentId column. Instead there is a parentTitle…
Zach Smith
  • 2,430
  • 13
  • 34
  • 65
4
votes
1 answer

Joining multiple subqueries

I need a query that returns something in this format: pk id v1 v2 v3 v4 v5 ---------------------- ... pk1 id1 A1 A2 A3 A4 A5 pk6 id2 B1 B2 B3 B4 B5 ... My current data looks like this CREATE TABLE foo(pk,id,value) AS VALUES ( 'pk1' , 'id1', …
Sassan
  • 141
  • 1
  • 6
3
votes
1 answer

Return recursive self joins in PostgreSQL with aggregated data

I have this simple table schema: SQL Fiddle PostgreSQL 9.6 Schema Setup: CREATE TABLE IF NOT EXISTS users( id INT NOT NULL PRIMARY KEY, email TEXT NOT NULL UNIQUE, password TEXT NOT NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL,…
Sirjon
  • 165
  • 6
3
votes
2 answers

Joining two ltree based tree tables recursively

I have two tables, roles and permissions, both of which use ltree for maintaining a tree structure. I also have a pivot table, roles_permissions, which serves to connect the two tables. How can I join them to each other – and bring in all the…
Forest
  • 131
  • 2
3
votes
1 answer

Avoid duplicates on self join

Given the following table my_table : my_column ------ A B C D I would like to be able to join on itself but without duplicate pairs like so: -- SELECT a.*, b.* FROM my_table a JOIN my_table b ON a.my_column <> b.my_column; -- something like so but…
Dionys
  • 133
  • 1
  • 5
3
votes
1 answer

Help on Self-Referential CTE SQL SERVER

I have this table below which is has a hierarchy to it where the number of levels vary, for this example I chose a subset with 7 levels. L_Key Parent Level P_Key ------------------------- 1393 NULL 0 2 1399 1393 1 325 4485 …
Kevin Nguyen
  • 53
  • 1
  • 2
  • 5
2
votes
1 answer

Best practices for large JOINs - Warehouse or External Compute (e.g. Spark)

I am working on a problem that requires a very large join. The JOIN itself is pretty straightforward but the amount of data I am processing is very large. I am wondering for very large JOINs, is there a preferred type of technology. For example, is…
Arthur Putnam
  • 553
  • 2
  • 6
  • 12
2
votes
0 answers

SQL Server - Self join on table is very slow

Working with Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64) We have a single table, which is planned to contain about 1.5 - 2, maybe max 3 million records at a time. Records will continuously be moved into historical tables.…
Gábor Major
  • 163
  • 1
  • 7
2
votes
1 answer

Looking for help reporting duplicates and null marks

Someone over at StackOverflow suggested this might be the place to ask this. I hope this will question is proper for this forum. I am looking for suggestions / criticisms of alternative methods of finding errors in an Oracle table. I am trying to…
FocusWiz
  • 135
  • 7
1
vote
1 answer

Issues with Self-Referencing Foreign Key in SQLite

I'm having trouble with a self-referencing foreign key in SQLite. I've created a table to store employee details, where each employee can have a manager who is also an employee. Here's the table definition: CREATE TABLE Employees ( Employee_ID…
reubenjohn
  • 111
  • 3
1
vote
1 answer

Multiple parents for AspNetUsers table for Asp.Net Core 3.1 app

I have the following table in MySQL.: CREATE TABLE `aspnetusers` ( `Id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `UserName` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, …
Hello World
  • 11
  • 1
1
vote
1 answer

Bidirectional self join table

Consider: CREATE TABLE object ( id integer NOT NULL DEFAULT nextval('object_seq'), ..., CONSTRAINT object_pk PRIMARY KEY (id) ); CREATE TABLE pairing ( object1 integer NOT NULL, object2 integer NOT NULL, …
Some_user
  • 61
  • 8
1
2 3 4