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

How can I optimize this MySQL query that involves two left joins?

I cannot figure out why my query slows down. What it boils down to are four tables: team, player, equipment, and metadata. Records in player and equipment have a FK to team, making team a parent of player and equipment. And all three of those…
mcsnolte
  • 207
  • 2
  • 9
2
votes
3 answers

LEFT JOIN doesn't work with count

I have a problem with my query: SELECT table1.Name, COUNT(*) FROM table1 LEFT JOIN table2 ON table2.Name_all = table1.Name GROUP BY table1.Name It shows and counts a names from table1. I want to join all names from table2 which do not…
Juronis
  • 495
  • 4
  • 8
  • 18
2
votes
4 answers

Mysql FULLTEXT search with GROUP BY, keep row value that has the highest score

In two FULLTEXT searches, I look for the search terms in the title of the book and the tags and get the following result: rScore tScore ID ... 1.235689725827653 0 406 0.928482055664062 …
NaturalBornCamper
  • 3,675
  • 5
  • 39
  • 58
2
votes
3 answers

Filtering LEFT JOIN results

I have two tables: authorizations and settlements. 'Settlements' contains a forign key reference to authorizations. A settlement can also have a status (ERROR, ACCEPTED, etc). Given this data: Authorizations Settlements id …
MikeQ
  • 1,817
  • 3
  • 19
  • 27
2
votes
1 answer

MySQL Query Optimization (7.6s to Run)

Heres my query: SELECT `users`.`id`, `users`.`username`, `users`.`profile_picture_url`, `users`.`credits_offered`, `users`.`country_id`, `users`.`user_level`, `users`.`credits` FROM `users` LEFT JOIN `history` ON `history`.`actor_id` = 100 AND…
Brad
  • 195
  • 2
  • 11
2
votes
3 answers

left join in rails/mysql

i am newbie in rails and try to perform left join in mysql. there are two objects - user and message. user has_and_belongs_to_many messages, message has_and_belongs_to_many users currently, by simply writing user.messages i get following query in…
Pavel K.
  • 6,697
  • 8
  • 49
  • 80
2
votes
2 answers

Mysql Sum distinct row

I want to sum the value of 3 columns from 3 tables My query: SELECT sum(s.paid_money+d.paid_money+e.paid_money) FROM payment_dates AS dates LEFT JOIN supplier_payments AS s ON dates.id = s.payment_date_id LEFT…
Khaled Lela
  • 7,831
  • 6
  • 45
  • 73
2
votes
1 answer

MySQL nested SELECT too slow

I have a script of the following structure: SELECT SUM(CASE WHEN pf.info IS NOT NULL THEN 1 ELSE 0 END) FROM summary s LEFT JOIN (SELECT id, info FROM items GROUP BY id) pf ON s.id=pf.id GROUP BY s.date What I want is to count those id's which are…
sashkello
  • 17,306
  • 24
  • 81
  • 109
2
votes
3 answers

:joins | change behavior inner join to left join

I have two tables 'users' and 'lms_users' class LmsUser belongs_to :user end class User has_one :lms_user end When I write User.all(:joins => :lms_user) it joins tables with Inner join. I want a Left join. How can change this behavior? (I…
Moosa
  • 127
  • 6
2
votes
1 answer

Mysql LEFT JOIN gets very slow above 25.000 rows

The following query becomes suddenly extremly slow (seems not to finish at all) with more than approx 25.000 rows in table a (tried with adding limit x to the query): select a.*, b.column from table_a a left join table_b b on (a.message_id =…
Cord
  • 23
  • 3
2
votes
1 answer

Is this the proper way to left join a table based on column value

I need some help with conditions on left join, my query is as follows declare @emp table (id int, name varchar(100)) insert into @emp values (1,'Emp1') insert into @emp values (2,'Emp2') insert into @emp values (3,'Emp3') insert into @emp values…
Harsha
  • 1,161
  • 4
  • 18
  • 38
2
votes
1 answer

SQLITE multiple table join with a condition

I have these tables: doodhiya dhid INTEGER PRIMARY KEY NOT NULL, dname TEXT NOT NULL, dfname TEXT NOT NULL, dage INTEGER NOT NULL, dadd TEXT, dphone INTEGER NOT NULL, demail TEXT NOT NULL doodhdata dtid INTEGER PRIMARY KEY NOT NULL, ddate…
Imran Khan
  • 63
  • 9
2
votes
2 answers

Query to get records present in one table but NOT in the other

I have two tables: teacher_lm and teacher. These tables have both the column "teacher_email" What I need is to get the emails that are present in teacher_lm but not in teacher. I have 2 different approaches to solve this, but I don't understand why…
Vika
  • 391
  • 5
  • 16
2
votes
1 answer

Multiple LEFT JOINs to self with criteria to produce distribution

Although several . questions . come . close . to what I want (and as I write this stackoverflow has suggested several more, none of which quite capture my problem), I just don't seem to be able to find my way out of the SQL thicket. I have a single…
user1600439
  • 23
  • 4
2
votes
5 answers

LEFT JOIN not working as expected with sub-query

I've got the SQL query below: SELECT message, sent_date, user_id FROM messages LEFT JOIN numbers ON messages.from_id = numbers.id It returns all the rows (about 4000) in the messages table with additional columns coming from the numbers table. So…
laurent
  • 88,262
  • 77
  • 290
  • 428
1 2 3
…
99
100