Left Outer Join Not Working

Left Outer Join Not Working?

You should move the constraints on prescriptions.filldate into the ON condition of the join, and remove it from the where clause:

LEFT OUTER JOIN prescriptions ON prescriber.dea_no = prescriptions.dea_no
                             AND prescriptions.filldate >= '09-01-12'
                             AND prescriptions.filldate <= '09-17-12'

Otherwise, entries for which there are no prescriptions end up with nulls in prescriptions.filldate, and the WHERE clause throws them away.

Left Outer Join not working as expected

move the condition on the WHERE clause to ON clause,

ON Users.idUser = Timesheets.id_User
   AND Timesheets.EndDate = CONVERT(DATETIME, '2014-08-15 00:00:00', 102)

The ON clause filters the rows before joining the table. The WHERE clause , on the other hand, filters out rows after the tables has been joined. Since you put the condition on the WHERE clause, Timesheets.EndDate with NULL values are excluded.

Why does Left Outer Join not work for attribute in different types in the tables in SQL?

Updated version (assuming that p_code and e_code are VARCHAR):

soelect * 
from E_Tab e
  left outer join P_Tab p
  on e.E_No=p.P_No
   AND SUBSTR(E.E_CODE,2,99) = P.P_CODE

Output:

E_NO    E_CODE  MINUTES P_NO    P_CODE  MINUTES
1   170 001 1506     170        01  1506
2   170 340 480  170        40  480
3   464 001 1440    464         01  1440
4   464 051 1440    464         51  1440
5   464 340 480 NULL    NULL    NULL

Precedent answer

Your posted query should work if e_no is NUMBER and p_no is CHAR or VARCHAR, with or without embedded spaces.
I think you didn't post the query you are using.
I can guess from your expected output you forgot

 AND E.E_Code = P.P_Code

or, if you sample data are correct:

 AND SUBSTR(E.E_CODE,2,99)=P.P_CODE

in you join clause.
Moreover, the value of E_CODE in your sample output is not the same in your sample data.

Example of output with your query and data, using P_NO as CHAR(8) with spaces:

insert into p_tab values (' 170 ' , 01, 1506)

+--+------+--------+---------+---------+--------+---------+
|  | E_NO | E_CODE | MINUTES |  P_NO   | P_CODE | MINUTES |
+--+------+--------+---------+---------+--------+---------+
|  |  170 |    340 |     480 |  170    |      1 |    1506 |
|  |  170 |      1 |    1506 |  170    |      1 |    1506 |
|  |  170 |    340 |     480 |  170    |     40 |     480 |
|  |  170 |      1 |    1506 |  170    |     40 |     480 |
|  |  464 |    340 |     480 |  464    |      1 |    1440 |
|  |  464 |     51 |    1440 |  464    |      1 |    1440 |
|  |  464 |      1 |    1440 |  464    |      1 |    1440 |
|  |  464 |    340 |     480 |  464    |     51 |    1440 |
|  |  464 |     51 |    1440 |  464    |     51 |    1440 |
|  |  464 |      1 |    1440 |  464    |     51 |    1440 |
+--+------+--------+---------+---------+--------+---------+

Problems getting LEFT OUTER JOIN to work

Many have answered, but I'll try too and hopefully lend in some more clarification. How I have always interpreted it (and you can check so many other posts I've responded to with LEFT joins), I try to list the table I want everything from first (left side... hence read from left to right). Then left join to the "Other" table (right side) on whatever the criteria is between them... Then, when doing a left join, and there are additional criteria against the right side table, those conditions would stay with that join condition. By bringing them into the "WHERE" clause would imply an INNER JOIN (must always match) which is not what you want... I also try to always show the left table alias.field = right table alias.field to keep the correlation clear... Then, apply the where clause to the basis criteria you want from the first table.. something like

select 
      a.id,
      a.name,
      ga.earned_epoch,
      ga.offline
   from   
      achievement a 
         LEFT OUTER JOIN gamer_achievement ga 
             ON a.id = ga.achievement_id
            AND a.game_id = ga.game_id
            AND ga.gamer_id = 'fba8fcaa-f57b-44c6-9431-4ab78605b024'
   where
      a.game_id = '1480656849'
   order by 
      convert (a.id, unsigned)

Notice the direct relation between "a" and "ga" by the common ID and game ID values, but then tacked on the specific gamer. The where clause only cares at the outer level of achievement based on the specific game.

Left join for 3 tables with Where not working as expected

Applying a condition on the "outer joined" table in the WHERE clause effectively turns the outer join into an inner join, because every row that is retained by the outer join will contain a null value in that column, but the condition c.Indicator = 'Y' in the where clause will remove those rows again.

To fix this, move c.Indicator = 'Y' into the join condition:

SELECT a.ID, b.Number, a.Version, c.Name, c.Indicator
FROM Version a 
  LEFT JOIN Cell b ON a.ID = b.ID
  LEFT JOIN Names c ON a.ID = c.ID AND c.Indicator = 'Y'
WHERE a.Version LIKE '1%' 

LEFT OUTER JOIN not returning results when second table does not have values

From what I can see, your inner join to StatusOption is based on CurrentStatusID from TimeCardHolder (tch2) which is a left join. If there is no row in tch2, then the inner join will end up with the results you are experiencing. Is there a way you could make StatusOption join (Extent5) a left join, and combine your where with the coalesce/isnull you already tried?

Full Outer Join Not Working - Need to return all dates regardless of null activity

You don't need a FULL JOIN here. You just need to start with Dates and LEFT JOIN everything else.

All the WHERE conditions relating to those other tables need to then go in the ON clauses.

Further notes:

• CAST(... AS date) is inefficient when used as a join or filter condition. It's better to use a date interval range. And if you have a Date table, it should just be declared as date data-type anyway. I haven't changed it as I don't know your data.
• d.MonthNameYear in (@parmDate) OR d.MonthNameYear is null is somewhat suspect: why would MonthNameYear be null on a dates table. And in (@parmDate) is strange: is it supposed to match a list? If so, that is not going to work.
• Furthermore, using OR like that is probably going to cause performance issues
• Use short and meaningful table aliases, it makes the query more readable

SELECT p.ProjectName,
       p.ProjectID,
       d.Date,
       d.MonthNameYear,
       u.id,
       u.Name,
       sum(ts.ActivityTime) as Time,
       e.ExpenseID,
       c.ClientName
FROM Dates d
LEFT JOIN
   (tClients c
    INNER JOIN tProjects p
        ON c.ClientID = p.ClientID
        AND (p.ProjectName in (@parmProjects) OR p.ProjectName is null)
    INNER JOIN tTimesheets ts
        ON p.ProjectID = ts.ProjectID
    INNER JOIN tUsers u
        ON ts.CreatedBy = u.id
        AND
        (u.Name in (@parmUser) OR u.Name is null)

    LEFT OUTER JOIN
       (tExpenses e
        INNER JOIN tExpenseType
            ON e.CategoryId = et.Id
       ) ON ts.CreatedBy = e.CreatedBy
           AND CAST(ts.ActivityDate AS date) = CAST(e.ExpenseDate AS DateTime)
           AND ts.ProjectID = e.ProjectID
)
  ON CAST(ts.ActivityDate AS date) = CAST(d.Date AS date)
   
WHERE        
(d.MonthNameYear in (@parmDate) OR d.MonthNameYear is null)

GROUP BY 
         u.Name,
         u.id,
         d.Date,
         d.MonthNameYear,
         p.ProjectName,
         p.ProjectID,
         e.ExpenseID,
         c.ClientName

ORDER BY d.Date;

An alternative is to place all the other joins in a derived table or CTE. This can sometimes be easier to write.

---

Related Topics