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

self join with inner join on other tables together

I have a database with 3 tables records, categories, relational. records (id, lat, lng) categories (c_id, c_value) relational (r_id, c_id) records id | lat | lng ---------------------- 1 23.57258 -35.28412 2 23.54855 -35.18881 3 …
arek
  • 1
  • 6
-1
votes
2 answers

SQL Query meeting condition on longitude AND latitude

I am using mySQL and my table's columns are as follows: post_id,meta_id,meta_value,meta_key Dummy data is attached as: I want data meeting the condition that latitude must be between 60 and 60.5 and longitude must be between 8 and 9. I am using…
Faizan Zahid
  • 13
  • 1
  • 7
-1
votes
2 answers

MySQL Self Join with a specific condition

My Question is, I have a table which is relatively big (~2 millions rows). Table is like ; id Action user_id Date ----------------------------------- 1 FOP userx date1 2 POP userx date2 3 FOP …
rom_pep
  • 87
  • 8
-1
votes
1 answer

SQL query issue using self join and count

Hello I'm trying to run a particular query for my final year project where I want to find out the number of undergraduate students studying computer science in 2014 & 2016. The tables I'm using are coursesEnrollments & courses to record the number…
Magan
  • 1
  • 3
-1
votes
1 answer

mysql self join returning blank values

I have the following mysql query select a.agency_name, a.green ,b.red, c.amber from (select count(action_status) as green, agency_name from tbl_actions where action_status='In Progress' group by agency_name) a join (select count(action_status) as…
Usman
  • 13
  • 1
-1
votes
3 answers

SQL Self Join Status Winner query

I have a customer table that has a self join (Parent - Child), I need to write a query that returns the child customer/s where the Status Allows the parent or child to place an order. The column is a bit column and is nullable. The results returned…
Paul
  • 1,103
  • 1
  • 13
  • 18
-1
votes
2 answers

sybase/sql self join multiple rows

Hi stackoverflow community, I'm trying to do a self join if the unique ID in Col 1 is the same. Table code: CREATE TABLE #table ( Unique_ID int, Product_code varchar(10) ) INSERT INTO #table (Unique_ID, Product_code) VALUES (1111111111, 1) …
henry91
  • 27
  • 5
-1
votes
2 answers

Join table on itself for distinct dates and count()

I'm on MYSQL 5.6 with a single table called ride +---------+-------+---------+ | Date | CarId | Airport | +---------+-------+---------+ | 2001-11 | 1 | JFK | | 2001-11 | 2 | JFK | | 2001-11 | 3 | LAX | | 2001-12 | 1 |…
Christian Haller
  • 643
  • 1
  • 7
  • 16
-1
votes
2 answers

SQL select join in same table by comparing 2 fields

I have the following table with these records as example: +------+---------+---------+ | key | amount1 | amount2 | |------|---------|---------| | A | 100 | 0 | | B | 0 | 100 | | C | 100 | 0 | | D | 66 …
Walid
  • 1
  • 1
-1
votes
2 answers

MySql - Self Join - Full Table Scan (Cannot Scan Index)

I have the following self-join query: SELECT A.id FROM mytbl AS A LEFT JOIN mytbl AS B ON (A.lft BETWEEN B.lft AND B.rgt) The query is quite slow, and after looking at the execution plan the cause appears to be a full table scan in the JOIN.…
mils
  • 1,878
  • 2
  • 21
  • 42
-1
votes
2 answers

What is a self join for? (in english)

I already know what a self-join does. Thank you, I also read all the other computerised operational descriptions on stack overflow, so I know this is not actually a duplicate question, so please do not give me tables or join lists. What I am seeking…
mist42nz
  • 97
  • 1
  • 8
-1
votes
1 answer

SQL - Query Self Join without executing it twice

I have a SQL query which takes much time to execute (like 30 minutes!) due to the huge data it takes. However, I need to do a couple of things more on this result a self join on this query. I can't create a temporary table on this database. What I…
Papa doc
  • 179
  • 2
  • 11
-1
votes
4 answers

Duplicates on Self Left Join

I'm trying to pivot out a table of data stored in a vertical model into a more horizontal, SQL Server table-like model. Unfortunately due to the nature of the data, I cannot use the real data here so I worked up a generic example that follows the…
Steven Ball
  • 461
  • 1
  • 5
  • 18
-1
votes
3 answers

SQLZoo Tutorial on Self Join

I tried to follow the hint by using self join twice but failed. Find the routes involving two buses that can go from Craiglockhart to Sighthill. Show the bus no. and company for the first bus, the name of the stop for the transfer, and the bus no.…
Ivy
  • 1
  • 1
-1
votes
2 answers

How to Use Join

I have table like id | name | manager 1 | A | 0 2 | B | 1 3 | C | 2 4 | D | 1 and i want each employee name along with manager name and id.
user3056158
  • 707
  • 2
  • 13
  • 20
1 2 3
…
84 85