Left Join With Where Clause

Left Join With Where Clause

The where clause is filtering away rows where the left join doesn't succeed. Move it to the join:

SELECT  `settings`.*, `character_settings`.`value`
FROM    `settings`
LEFT JOIN 
       `character_settings` 
ON     `character_settings`.`setting_id` = `settings`.`id`
        AND `character_settings`.`character_id` = '1'  

left join and where condition in joining condition

You should not use column related to left table in where condition (this work as a INNER JOIN) move the condition for left join in the related ON clause

 select *  
 FROM   table1 t1
 left join table2 t2
          ON t1.id = t2.fk_id AND t2.id_number = 12174
 WHERE  t1.code = 'CODE1' ;

The where condition is the equivalent part of the INNER JOIN clause this is the reason that you have this behavior..

adding the condition to the on clause mean that also the added condition work as an outer join ..

SQL Server LEFT JOIN and WHERE clause

SELECT ID, Name, Phone 
FROM Table1 
LEFT JOIN Table2 
ON Table1.ID = Table2.ID AND Table2.IsDefault = 1
WHERE Table1.ID = 12

How to replace a condition in left join through a where clause?

Your real tables probably look like this (primary keys bold):

• item (item_id, name, created_by_user_id)
• itemuserupdate (item_id, updated_by_user_id, name)
• users (user_id, name)

What you can do is get all user/item combinations first and then outer join the existing entries:

create myview as
select i.item_id, i.name, u.user_id, iu.name as name_by_user
  i.item_id,
  u.user_id,
from users u
cross join item i
left outer join itemuserupdate iu on iu.itemid = i.itemid
                                 and iu.updated_by_user_id = u.user_id;

You then then use this view with

select item_id, name, name_by_user from myview where user_id = 123;

MySQL query LEFT JOIN and WHERE clause

You need to move the condition on the left joined table from the where clause to the on part of the join. Otherwise, the condition becomes mandatory, and eliminates unmatched records from the resultset.

SELECT e.eventID, r.totalScore, r.playerID
FROM events e
LEFT OUTER JOIN results r ON e.eventID = r.eventID AND r.playerID=1

Left join with other table with where clause

move the where clause to the on clause:

select * from table1 as t1
left join table2 as t2 on t1.id = t2.table1_id
and myfield="test1"
group by t1.id

BTW: some DBMS does not allow select * with group by. So select the id and some aggregated values or remove group by id

LEFT JOIN returning no results if a WHERE clause is added?

When you include:

AND ehrs.Type = 'HOLIDAY'

in the WHERE clause, you actually get an INNER join instead of a LEFT join because you get only the matching rows from the table ers and not any of the unmatched rows for which ehrs.Type would be NULL.

What you can do is move the condition to the ON clause of the LEFT join:

LEFT JOIN dbo.EmpHRSchedule ehrs (NOLOCK) 
ON (e.EmployeeNo = ehrs.EmployeeNo) AND ehrs.Type = 'HOLIDAY' 

and you could also include the other conditions for the same table:

LEFT JOIN dbo.EmpHRSchedule ehrs (NOLOCK) 
ON (e.EmployeeNo = ehrs.EmployeeNo) AND ehrs.Type = 'HOLIDAY' 
   AND ehrs.STARTDATE >= @ps_StartDate
   AND ehrs.STARTDATE < @ps_EndDate

---

Related Topics