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

MySQL select in same table

What is the right SELECTsyntax for my request? Table: ID / PARENT_ID / THIRD_ID 60 / 0 / 123 61 / 60 / 345 62 / 61 / 567 63 / 61 / 789 My request: Show all THIRD_PARENT_IDs generated by IDs and PARENT_IDs. I.e. has entry with id = 62 the parent_id…
Matthias from Berlin
  • 959
  • 7
  • 12
-1
votes
1 answer

SQL Self-join with data comparison for different days

I need to compare data on two different days in SQL. And I really need that in a single query since I need to use the results in pagination. Problem is, when I'm doing a self join it's results in duplicate columns since INNER JOIN is a cartesian…
valk
  • 9,363
  • 12
  • 59
  • 79
-1
votes
1 answer

SQL Self-Join Query

I am a student taking an Oracle database class and for the life of me I cannot figure out this problem. I would really appreciate if you could help. Each human has one spouse, who is also a human. Table name: Humans Records: H# HName S# 101 Adam …
Bunny
  • 3
  • 1
-1
votes
1 answer

data retrive using self join in table

I am using self join to fetch data Table name MyInfo: +------+------+ | NAME | CITY | +------+------+ | a | null | | b | null | | null | c | | null | d | +------+------+ I want output as +------+------+ | NAME | CITY…
prabhu
  • 39
  • 6
-1
votes
5 answers

In MySQL how to use SQL to get the row with the most recent date

create table client ( client_id int, name varchar(20) ); insert into client values (1, 'Google'); insert into client values (2, 'Facebook'); create table client_log ( client_id int, dt date, status varchar(20) ); insert into…
davidjhp
  • 7,816
  • 9
  • 36
  • 56
-1
votes
3 answers

MySQL Self Join Not All Rows included

I have a problem working out a Self Join. I have the following table: CREATE TABLE `test`.`tableN` ( `id` int(10) unsigned NOT NULL auto_increment, `Group` int(10) unsigned NOT NULL, `Item` int(10) unsigned NOT NULL, `data` varchar(45) default…
IGGt
  • 2,627
  • 10
  • 42
  • 63
-1
votes
1 answer

View having self join on table

I want to make a view for which i will be requiring a query having self join in it. I am worried about its performance and behavior as it is going to be fetch some handsome amount of data. Details of table and query are as under (original table has…
Femme Fatale
  • 870
  • 7
  • 27
  • 56
-1
votes
2 answers

Find repeat datetimes within X secs - Returning duplicate times

The query I am using is: SELECT a.What_Tech, a.callid FirstCallid, b.Callid SecondCallid, a.[segStart_dateTime] FirstCallTime, b.[segStart_dateTime] SecondCallTime, DateDiff(second, a.[segStart_dateTime], b.[segStart_dateTime]) as…
CodingIsAwesome
  • 1,946
  • 7
  • 36
  • 54
-2
votes
1 answer

What would happen if I do a self join of a dataframe and also apply broadcasting?

What will happen if I join a dataframe/RDD/dataset with itself, i.e. do a self-join, and do a broadcast of the same dataframe/RDD/dataset in the operation? The broadcast and self-join can't work together optimally.
Sanket Mehta
  • 1
  • 1
-2
votes
1 answer

Top game with highest sales for each year

I'm using SQL Server with a table about video game sales from the years (1977-2020). The query below is what I've been using to solve the problem: finding the #1 game with the highest sales for each year. When executed the output gives me multiple…
Nickolas Jaramillo
  • 3
  • 1
-2
votes
1 answer

Repeat the Values in 'Null' places

We have multiple same id's and some of them have values and some of them are null, we wanted to copy the values where it is null. Current…
Ishwar
  • 1
-2
votes
1 answer

Check if value exists on one date missing on next

I have one table with below column Table I want to find missing nodes Missing node definition: node present on day and missing the next day will be missing node on next day In above example 111 will be missing on 2022-03-03 100 will be missing on…
shweta sabale
  • 1
  • 1
-2
votes
1 answer

SQL Self Join: Why is the right half of the table different than the left half?

SELECT DISTINCT a.num, a.company, a.stop,stopa.id id1, stopa.name Aname, b.num bnum,b.company bcompany ,stopb.name Bname, b.stop ,stopb.id id2 FROM route a JOIN route b ON (a.company = b.company AND a.num = b.num) JOIN stops stopa ON (a.stop =…
alexapodacaf
  • 1
-2
votes
1 answer

Relation emp-super

creat table supervisors ( person char(20) not null, supervisor char(20) not null, constraint supervisor-pk primary kye(supervisor), ); instr into supervisors value('Bob','Alice'); instr into supervisors value('Mary','Susan'); instr into…
Leen 2002
  • 1
-2
votes
2 answers

Is there a way to find the max value in a table of a self-join?

SELECT * FROM Trips T1 WHERE T1.Cost NOT IN (SELECT T2.Cost FROM Trips T1, Trips T2 WHERE T1.Cost > T2.Cost) This is currently my query that will return the most expensive trip listed in the itinerary. Of course, you can simply use MAX, but I have…
buckywucky
  • 45
  • 5
1 2 3
…
84 85