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

How do I convert old join line of code to new join syntax?

There is a section of code in one of my sqls that goes like this: FROM Table1 LEFT OUTER JOIN TABLE2 ON (Table1.Field1 = Table2.Field1)) LEFT OUTER JOIN TABLE3 ON(Table2.Field1 = Table3.Field1)) LEFT…
studentofarkad
  • 155
  • 1
  • 1
  • 8
3
votes
2 answers

MySQL UNION ALL (Full Join) with conditional aggregation

I have three tables: CREATE TABLE `Agreement` ( `AID` bigint(20) NOT NULL AUTO_INCREMENT, `FLAGS` bigint(20) NOT NULL DEFAULT '0', PRIMARY KEY (`AID`) ); CREATE TABLE `Assessment` ( `ASMID` bigint(20) NOT NULL AUTO_INCREMENT, `AID`…
rsudip90
  • 799
  • 1
  • 7
  • 24
3
votes
3 answers

PostgreSQL: left outer join syntax

I'm using PostgreSQL 8.4.6 with CentOS 5.5 and have a table of users: # select * from pref_users where id='DE2'; id | first_name | last_name | female | avatar | city | lat | lng | login | last_ip |…
Alexander Farber
  • 21,519
  • 75
  • 241
  • 416
3
votes
3 answers

How to use LEFT OUTER JOIN to identify missing products by vendor?

This query has been fun to figure out but I have come to place where I need some help. I have several tables and the ultimate question is: How many total parts are "missing", by vendor? and / or How many total parts are "missing", by vendor…
lcm
  • 1,737
  • 6
  • 17
  • 40
3
votes
2 answers

mysql INNER JOIN but also NULL when not exist

Ok, I'm trying to get this done the whole day but my knowledge in MySQL seems too limited. I've got following tables: time_entries |id|comment|ticket_id| | 1|foo | 1| | 2|bar | 1| | 3|baz | 2| | 4|lorem | 3| |…
shooby
  • 95
  • 2
  • 8
3
votes
2 answers

SQL: Left Outer join that can switch what columns to join with depending on data inside the column

I'm trying to join these two tables together to show the record in base and the null in history. The problem I am having is with a left outer join. when BO.val2 = 0: I want the join to use BO.VAL5 = TR.VAL5 only but when BO.val2 = TR.val2 I want it…
Beau Jenkins
  • 31
  • 2
3
votes
3 answers

Using traditional join clause with the (+) operator

I'm really stuck with a SQL question from the JustLee Database. NOT looking for anyone to do my homework but unfortunately I am stuck... Questions: Show all authors and books they have written. Include authors that haven't finished writing their…
DNkG
  • 49
  • 1
  • 6
3
votes
1 answer

CUBE + outer join = extra NULL row

When I use PostgreSQL's CUBE on a query with an OUTER JOIN, I get an extra all-NULL row that can't be distinguished from the cube's own "everything combined" all-NULL result. CREATE TABLE species ( id SERIAL PRIMARY KEY, name …
Catherine Devlin
  • 7,383
  • 2
  • 25
  • 17
3
votes
2 answers

Yet another LEFT OUTER JOIN fail in SQL Server 2012 Express

I'm wondering if someone might be able to troubleshoot my query. I have a simple table that has project savings per month. There are always 12 consecutive months worth or savings, but the first month can vary (e.g.: start from January for 12 months,…
Aiden Dipple
  • 134
  • 2
  • 15
3
votes
3 answers

Oracle Plus (+) Joins to ANSI conversion

I'm in the middle of a warehouse migration from Oracle to SQL Datawarehouse Azure and ran into an issue with this query. The original query from Oracle - it returns 1872520 rows. SELECT * FROM STG_REV_APPORTION_CSC_NO t1, STG_SEP_VL t2, …
RK Kuppala
  • 356
  • 1
  • 2
  • 14
3
votes
1 answer

How to implement Left or right outer join in QueryDsl

How to implement Left or right outer join in QueryDsl. A simple example is helpful.
user1456650
  • 41
  • 1
  • 6
3
votes
4 answers

Left outer join in linq

I have the following query but i have no idea on how to do a left outer join on table 1. var query = (from r in table1 join f in table2 on r.ID equals f.ID select new { …
zSynopsis
  • 4,854
  • 21
  • 69
  • 106
3
votes
0 answers

Spark imbalanced partitions after leftOuterJoin

I have a pattern like this... psuedo-code, but I think it makes sense... type K // key, function of records in B class A // compact data structure val a: RDD[(K, A)] // many records class B { // massive data structure …
kmh
  • 1,516
  • 17
  • 33
3
votes
3 answers

SQL-Duplicate column names

I am using SQL developer and I am trying an outer join on two tables. The error it is showing is "Duplicate column names". I have used the table nameswhile comparison but still it is giving error. Code is as following: CREATE VIEW OECD_VIEW…
Shubham Goyal
  • 617
  • 1
  • 7
  • 18
3
votes
4 answers

Get me the clear picture of outer joins in Oracle 9i

Outer joins seem to me a little bit confusing. Is there anyone can get me a clear picture of outer joins (right, left and full)?
Gangu
  • 171
  • 1
  • 2
  • 10
1 2 3
…
99 100