Stack Overflow
Stack Overflow

Questions tagged [outer-join]

An outer join defines a relationship between two tables where all records from one or both tables are returned regardless of the existence of a matching key-field in the other table. A full outer join combines the results of both tables. A left or right join returns all the records from the first or second specified table, respectively. NULLS are filled in for matches on either side. A self-join compares a table to a copy of itself.

A full combines the results of both tables. A left or right returns all the records from the first or second specified table, respectively. NULLS are filled in for matches on either side. A compares a table to a copy of itself.

References

1671 questions
-1
votes
1 answer

MySQL to fill in the blanks with NULLs when their isn't a row based on set number of IDs from the join

Looking to fill in the blanks with NULLs when there isn't a record that is for a row that I am expecting. Consider the following wp_posts (p) ID post_title 1 Week 1 2 Week 2 3 Week 3 4 Week 4 5 Week 5 wp_users…
Bobby S
  • 4,006
  • 9
  • 42
  • 61
-1
votes
1 answer

Postgres: Select multiple but show records where linked field isn't present

I have multiple tables in my Postgres database that are linked by a field called id. My main table, Person is linked to other tables Address, Phone andEmail by id. This line of code gets information about the person from all tables in the…
George Shalhoub
  • 1
  • 1
-1
votes
2 answers

Why FULL OUTER JOIN 2 Tables not returning all rows of those tables

I have 2 tables like below Table Demographics2 CREATE TABLE Demographics2 (Date date, FirstID int, SecondID int, FirstName varchar(50), LastName varchar(50), DCode int ) ; Insert into Demographics2 VALUES ('20200402', 342, 812, 'John',…
Shichimi
  • 71
  • 8
-1
votes
1 answer

Compare 2 databases where tables in each database share the same column data

I'm using 2 databases [Elg] and [VTrader] Within [Elg] there are 3 tables( ElgFileQueue, RXFileQueue, and CostFileQueue) and each shares 2 columns called FileName and FileCatalogedOn. The FileName data is also shared in [VTrader] under the column…
Mistymanor
  • 5
  • 2
-1
votes
1 answer

Add filter on left outer join in oracle

I Have a scenario where there are 3 tables, First table have id, name. Second table have id, status, Third table have id, dept_id. First table is the parent table and have superset of data. Second table doesn’t have all the records while third have…
Sri
  • 1
  • 1
-1
votes
2 answers

Subqueries - Finding the average number of tracks per album

I am trying to query how many songs on average are on albums with the word "Rock" in the title. The chosen albums must have at least eight songs on them. ER diagram SELECT AVG(tr.track_id) FROM (SELECT al.album_id AS album, tr.name, …
Maor Ohayon
  • 1
  • 2
-1
votes
2 answers

SQL three tables using only JOIN

I have three tables: T1 has id's from T2 (client) and T3 (supplier), it also acts a black list: T1's rows with client's and supplier's id. I want to get the supplier's id the client can buy from. . currently T1 has three rows: T1_id | T2_id |…
Vinícius OA
  • 54
  • 6
-1
votes
1 answer

Which join method is comparatively better for eg: (+) or Right outer join

In terms of performance, which join method is better. And what are the drawbacks doing this way Tab1.col1 = Tab2.col2(+)
rani james
  • 23
  • 4
-1
votes
1 answer

How to access columns after doing outerjoin in sqlalchemy

I did outerjoin of two tables. And I got the correct result. But I don't know how to access the Columns in the result. Below is my code. result = db.session.query(Purchase, Product.pr_id).outerjoin(Product, Purchase.id ==…
Gireesh
  • 111
  • 1
  • 7
-1
votes
1 answer

Join to grab only non-matching records SQL

I have some data which I'm trying to clean in order to run further analysis on. One of the columns in the table is called record_type, this can either be NEW or DEL. This means that initially a NEW record might be added but then a DEL record would…
qazwsx123
  • 237
  • 4
  • 11
-1
votes
2 answers

Full outer join not giving the answer I need

I am using PostgreSQL and am having difficulty with getting a series of queries that combine the data from two tables (t1, t2) t1 is studyida gender age a M 1 a M 2 a M 3 b F 4 b F 5 b F 6 c M 13 c M 14 c M 15 and t2…
user918967
  • 2,049
  • 4
  • 28
  • 43
-1
votes
2 answers

FULL JOIN query results in Error Code: 1054. Any workaround?

I'm trying to join two tables to show all rows that match the where clause, not just the ones that match the join. It's two tables of bills and I am trying to merge them so that each row with matching year and month from both tables has a few fields…
Steve Seeger
  • 1,409
  • 2
  • 20
  • 25
-1
votes
3 answers

left join not returning left hand side

SELECT U.UserID, U.FirstName, U.LastNAME,'2022-10-07' as TheDate, ES.DateWorking frOM Users U LEFT JOIN EventsStaff ES on U.UserID = ES.UserID WHERE ES.DateWorking = '2022-10-07' I can't see what is wrong with the above. There isn't an entry in…
P Whittaker
  • 89
  • 1
  • 8
-1
votes
1 answer

Why and how do these two queries both work

I have been trying to learn SQL using SQLBolts tutorial and for this exercise, I needed to write a query that showed the names of all the buildings with no employees using only LEFT JOIN. I had an answer different from the website and I am wondering…
Zizi Musoke
  • 9
-1
votes
1 answer

Full join with MS SQL Server

I wanted to use full join to solve this leetcode question: https://leetcode.com/problems/reformat-department-table/ The issue is that since not all months are in all department IDs, so I'm getting a Null row which should be targeted for department…
KarlTMuahaha T
  • 63
  • 4
1 2 3
…
99
100