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

SQL - outer join with count

I have 3 files Order Header- has order# Order Detail - details lines with product code and qty , keyed on order# Consigned Item file - list of consigned products (think products that we sell but are owned by someone else). not all products we sell…
Abe
  • 298
  • 4
  • 12
0
votes
2 answers

Oracle outer join not working as expected

I have the following query: select * from per_all_assignments_f paaf, pay_all_payrolls_f payr where sysdate between paaf.effective_start_date and paaf.effective_end_date and paaf.assignment_type in ('E', 'C') and paaf.primary_flag…
Superdooperhero
  • 7,584
  • 19
  • 83
  • 138
0
votes
1 answer

Hibernate Named Query Outer Join

Let's say I have the following tables: TABLE1: ID number NAME varchar TABLE2: ID number (foreign key from first table) KEY varchar VALUE varchar Table 2 could have records for elements in table 1, but not always. I need a Named Query that will get…
Martin
  • 3,018
  • 1
  • 26
  • 45
0
votes
3 answers

SQL: outer join two tables on String and insert similar into another

I have two tables A and B with 30 columns (same variable names, data for different year), no primary key, almost a million records each. I want to compare A.X1 with B.X1 (nvarchar8, contains spaces, -, letter and numbers) and insert the outer join…
Siftekhar
  • 153
  • 2
  • 7
0
votes
1 answer

Search using CROSS APPLY SQL Statement

I have the following SQL statement which returns results from a database in the format I need. However, I would like to use this query but add a search for where JobProducts.Serial = x How can I add such a search? SELECT J.CustomerID, J.JobID,…
user1711657
  • 83
  • 1
  • 7
0
votes
3 answers

Mysql : Products which satisfies category conditions

I want only those products which satisfies following condition : Which have ((category_id = 1 OR category_id = 2) AND (category_id = 3 OR category_id = 4)) Database structure: products id int(10) name varchar(128) categories Id int…
Nishit Modi
  • 175
  • 1
  • 18
0
votes
2 answers

T-SQL multiple outer join with composite key

I have two source views: View1 ----------------------------------------------------- | UserId | RoleId | Total1 (calculated value) | ----------------------------------------------------- | 1 | 21 | 9 …
boj
  • 10,935
  • 5
  • 38
  • 57
0
votes
2 answers

LEFT OUTER JOIN not showing all rows?

I'm trying to get data from 2 tables with one SQL statement using joins. The idea is quite simple. A project has participants, and in a project overview I want to show the project info with the amount of participants. Right now there are 2 projects,…
user936965
0
votes
2 answers

Outer apply on another outer apply?

I have a problem with an SQL request I am working on. I would like to return the total number of a certain item that is ordered by a certain customer. The problem is: I get more than one line when I execute my request. (Please note that this is a…
AquaSoley
  • 45
  • 1
  • 1
  • 6
0
votes
2 answers

Ordering in MySQL Bogs Down

I've been working on a small Perl program that works with a table of articles, displaying them to the user if they have not been already read. It has been working nicely and it has been quite speedy, overall. However, this afternoon, the performance…
Timothy R. Butler
  • 1,097
  • 7
  • 20
0
votes
1 answer

Left outer join using LINQ - yields different outputs

I am trying to write left outer join using LINQ. The SQL looks like, SELECT * FROM Table1 td1 LEFT OUTER JOIN Table2 ON td1.ColumnName = td2.ColumnName WHERE td2.ColumnName IS NULL ORDER BY SomeColumns If I run…
VJOY
  • 3,752
  • 12
  • 57
  • 90
0
votes
1 answer

hibernate: left outer join with Criteria API, is possible?

I'm new on Hibernate so excuse me for banality but I can't find any answer related to my problem (i tried to search on docs & on SO). If possible, I would create a left outer join from two tables (t0 and t1) without using HQL; in my case I'd like to…
mrddter
  • 59
  • 8
0
votes
2 answers

SQL query: return values even if they don't match

2I have the following two tables with the associated fields: Values: id, data, quantity Items: id, data, price The match depends on the fields: id, data. I'm trying to join the two tables so that if for the very same id and data there is an element…
Camilla
  • 949
  • 2
  • 14
  • 26
0
votes
1 answer

using outer join in mysql to join child tables

I have a newbie question about outer joins. I have 5 tables with the following structure. employee (employee_id, name, address) employee_benefits (employee_id, benefit_id, join_date) insurance (insurance_id, name) insurance_benefits (insurance_id,…
Kevin Handy
  • 1
0
votes
1 answer

Linq outer join on non-nullable field

Using some logic mentioned in this answer, I'm using this code to perform outer joins on a few tables. I know that one of the three joins will yield a MyField value. However, in my select statement, I'm calling a constructor, and I only want to use…
WEFX
  • 8,298
  • 8
  • 66
  • 102
1 2 3
…
99 100