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
2 answers

Why does using my SELF-JOIN gives me error?

select 1.jmeno, 1.nadr from zam as 1, zam as 2 where 1.nadr = 2.nadr group by 1.jmeno, 1.nadr;
Simon Dlesk
  • 1
-1
votes
2 answers

Fetch certain records from db using SQL

I have a table with the multiple records where PLACE_ORDER entry is not there i.e order#594886579291. I want to fetch only order where PLACE_ORDER row is not there like 594886579291. If I do a simple query like SELECT * FROM ORDER_STATUS os WHERE…
Vijay Kumar Rajput
  • 1,071
  • 1
  • 10
  • 30
-1
votes
1 answer

MySQL self join to get ordered parent-child records

I have a table with the following structure: +-----------+--------------+-----------+----------+ | member_id | household_id | firstName | lastName | +-----------+--------------+-----------+----------+ | 14122 | 0 | Cynthia | Bookout…
Osama Ibrahim
  • 148
  • 10
-1
votes
1 answer

How do I select pairs from table with distance less than x?

In my database, I have a table of houses. Each house has a street id and a distance in meters from the start of the street. I want to find all pairs of houses along the same street that are in a distance less than e.g. 20 meters from each…
Matthias Bohlen
  • 598
  • 6
  • 12
-1
votes
1 answer

Rails 5.0: Add self join table that references existing table

We're creating a flight scheduling program that schedules employees and planes for flights. Here are the models that currently exist Airport Pilot FlightAttendant Aircraft We are starting with only four airports. We want to fill in the four airports…
ABvsPred
  • 67
  • 7
-1
votes
1 answer

SQL Server: Self Join query; select only records with matching first name WITHOUT a where statement

I am completely new to SQL Server. I'm stuck on a lab question. I cannot use a WHERE statement to limit the results. I attached the directions I was given below. Expected result should return 6 rows. I am currently returning 122 rows.We are using…
Chris
  • 1
  • 2
-1
votes
1 answer

Redshift: Finding first and last events in session without any session id

I have a Redshift table of users' events in mobile-app: |user| screen |collector_timestamp| --------------------------------------- |1111| StartScreen|2018-10-01 07:02:33| |1111|FinishScreen|2018-10-01 07:02:34| |1112| OrderScreen|2018-10-01…
koch_kir
  • 163
  • 17
-1
votes
1 answer

Updates and Self Joins and Case Statements - oh my

I think I may be trying to do too much with one query, and it's been driving me batty. I have two tables, Source and Zip_Code. The Source table has a zip code field that can either be 3 or 5 digits. If it's 3 digits, I need to join to the zip_code…
Carduus
  • 113
  • 6
-1
votes
1 answer

what is the advantage of using self-join in mysql?

what is the exact purpose of self-join apart from "referencing itself". what exactly we can find by doing this? from w3schools i get it. they want to shows the customers that live in the same city. but doesn't it make more confused? because it shows…
bernard
  • 21
  • 1
-1
votes
3 answers

how to join a single date column to a time rage table

table 1 table columns are: cancel_date product total_cancels 6/1/2017 a 100 6/1/2017 b 40 6/2/2017 b 10 6/3/2017 b 20 . . . 6/1/2018 a 40 6/1/2018 b 10 table…
joey
  • 115
  • 1
  • 1
  • 10
-1
votes
1 answer

T-SQL > Self Join > where clause not working

This will be my very first post, hopefully I'm posting this question with enough detail & making it specific enough. Firstly, I'm joining a table to itself (Structure s1 JOIN Structure s2). This table is then joined to another table (planning_entity…
Merla
  • 23
  • 5
-1
votes
3 answers

Postgres self join multiple times

Suppose we have this table No Date Value 1 2018-02-20 12:00:00 200 1 2018-02-20 12:05:00 205 2 2018-02-20 12:00:00 205 2 2018-02-20 12:05:00 200 3 2018-02-20…
Vanessa Zorgia
  • 13
  • 3
-1
votes
3 answers

SQL query with a self join

I have a table something like create table test ( id [varchar](3), var1 [varchar](2)) insert into test values (001, 'X1') insert into test values (001, 'X2') insert into test values (002, 'X3') insert into test values (002, 'X4') insert into test…
Manoj Agrawal
  • 775
  • 3
  • 8
  • 20
-1
votes
1 answer

How to remove duplicates from table using join in mysql

select * from ( SELECT id, imei1, status FROM `owarranty_imei` mto WHERE EXISTS ( SELECT 1 FROM `owarranty_imei` mti WHERE mto.imei1=mti.imei1 LIMIT 1, 1 ) ) t1 left join `owarranty_warranty_activations` as t2 on…
Sriya
  • 167
  • 2
  • 3
  • 10
-1
votes
2 answers

How can I do a self-join in Laravel?

Here is my code: $role_id = Auth::user()->role_id; $related = Page::where('path', $request->path())->where('method', $_SERVER["REQUEST_METHOD"])->first()->related; $pages = Page::where('related', $related)->get(); foreach ($pages as $page){ …
stack
  • 10,280
  • 19
  • 65
  • 117
1 2 3
…
84 85