Stack Overflow
Stack Overflow

Questions tagged [left-join]

A left join is an outer join which generates a result that contains all the records of the "left" table even when there are no matching records in other tables taking part in the join.

SQL LEFT JOIN ON returns the rows of INNER JOIN ON plus unmatched left table rows extended by NULLs. A consequence is that it returns all the rows from the left table at least once even if there are no matches in the left table. So if the ON clause matches 0 (zero) records in the left table then the join will still return that row extended by a NULL in each column from the right table.

Specific join tags

You can specify your question by adding extra tags:

Questions

8172 questions
54
votes
3 answers

MySQL: How do I join same table multiple times?

I have two tables ticket and attr. Table ticket has ticked_id field and several other fields. Table attr has 3 fields: ticket_id - numeric attr_type - numeric attr_val - string attr_type is a fixed enum of values. For example, it can be 1, 2 or…
rmflow
  • 4,445
  • 4
  • 27
  • 41
51
votes
3 answers

MySQL - sum column value(s) based on row from the same table

I'm trying to get 'Cash', 'Check' and 'Credit Card' totals in new columns based on ProductID from the same table. Table - Payments +-----------+------------+---------------+--------+ | ProductID | SaleDate | PaymentMethod | Amount…
sajinshrestha
  • 735
  • 1
  • 6
  • 11
49
votes
4 answers

HQL left join of un-related entities

I have 2 entities, A and B. They are related but I do not want to add the relationship mapping to the beans. How can we use left outer join between A and B using HQL or criteria? There are some workarounds available for this, Use Native SQL as told…
ManuPK
  • 11,623
  • 10
  • 57
  • 76
46
votes
4 answers

Delete from one table with join

I'm trying to delete records from one database based on a selection criteria of another. We have two tables, emailNotification which stores a list of jobs and emails. Then we have jobs. I want to clear out emailNotifications for jobs that have been…
Julian Young
  • 872
  • 2
  • 9
  • 21
46
votes
2 answers

Query with LEFT JOIN not returning rows for count of 0

I am trying to get the following to return a count for every organization using a left join in PostgreSQL, but I cannot figure out why it's not working: select o.name as organisation_name, coalesce(COUNT(exam_items.id)) as total_used …
mulus
  • 475
  • 1
  • 4
  • 7
46
votes
7 answers

SQL LEFT JOIN return 0 rather than NULL

I want to join two tables, with the number of records for each type being counted. If there are no records of that type in the left table I want a 0 to be returned, not a null. How can I do this?
Karl
  • 1,257
  • 4
  • 14
  • 16
45
votes
1 answer

MYSQL Left Join COUNTS from multiple tables

I want to add columns that represent counts from other tables. I have 3 tables. Messages MessageID User Message Topic 1 Tom Hi ball 2 John Hey book 3 Mike Sup …
Tom
  • 917
  • 2
  • 12
  • 23
44
votes
5 answers

SQL Server Left Join With 'Or' Operator

I have a four tables, TopLevelParent, two mid level tables MidParentA and MidParentB, and a Child table which can have a parent of MidParentA or MidParentB (One or the other midParent must be in place). Both mid level tables have a parent table of…
Declan McNulty
  • 3,194
  • 6
  • 35
  • 54
43
votes
2 answers

Symfony2 QueryBuilder join ON and WITH difference

I'm new with Symfony2 and I built successfully my first join through QueryBuilder and Doctrine 2. Probably this is a stupid question but both on-line and in the Symfony2's methods I was unable to find anything for understanding the difference…
Roberto Rizzi
  • 1,525
  • 5
  • 26
  • 39
42
votes
7 answers

Using left join and inner join in the same query

Below is my query using a left join that works as expected. What I want to do is add another table filter this query ever further but having trouble doing so. I will call this new table table_3 and want to add where table_3.rwykey =…
Will
  • 1,084
  • 5
  • 20
  • 42
42
votes
6 answers

Isn't SQL A left join B, just A?

I was looking at a few graphs to understand the difference between the joins, and I came across this image: Maybe the problem is in representing this with Venn Diagrams. But looking at the first join, top left, isn't that just A? What difference…
user4307777
42
votes
6 answers

LINQ Inner-Join vs Left-Join

Using extension syntax I'm trying to create a left-join using LINQ on two lists that I have. The following is from the Microsoft help but I've modified it to show that the pets list has no elements. What I'm ending up with is a list of 0 elements. I…
Guy
  • 65,082
  • 97
  • 254
  • 325
42
votes
2 answers

SQL LEFT-JOIN on 2 fields for MySQL

I have a view A and a view B. In A I have a lot of information about some systems, like IP and port which I want to preserve all. In B I have just one information that I want to add at A. The matching fields between the two views are IP and Port. So…
Possa
  • 2,067
  • 7
  • 20
  • 22
41
votes
3 answers

Postgres: left join with order by and limit 1

I have the situation: Table1 has a list of companies. Table2 has a list of addresses. Table3 is a N relationship of Table1 and Table2, with fields 'begin' and 'end'. Because companies may move over time, a LEFT JOIN among them results in multiple…
Hikari
  • 3,797
  • 12
  • 47
  • 77
39
votes
5 answers

Deleting using LEFT JOIN

I want to delete from a table depending on data that exists on another table that references the first, however, I have the code that works and shows the value to be deleted when I run it as a SELECT stetement, however when I change that to DELETE…
Matt
  • 1,471
  • 8
  • 20
  • 28
1 2
3
…
99 100