Issues with left join

Question

I am trying to figure what is wrong i am doing

Here is the JOIN i have

select * from table1 left outer join table2 on 
            table1.fkID = table2.fkID
            where table2.fkID= 500 order by 1 desc

i should multiple records but i am only getting 1, am i doing something wrong here

Side Note: [Bad Habits to Kick : ORDER BY ordinal](https://sqlblog.org/2009/10/06/bad-habits-to-kick-order-by-ordinal). This could especially problematic considering you are using `SELECT *`. — Thom A, Dec 01 '20 at 16:38

score 1 · Answer 1 · edited Dec 01 '20 at 16:32

1

You need to move the condition in the where clause to the on clause:

select *
from table1 left outer join
     table2
     on table1.fkID = table2.fkID and
        table2.fkID= 500
 order by 1 desc;

The where clause filters out NULL values turning the outer join into an inner join.

edited Dec 01 '20 at 16:32

Venkataraman R

answered Dec 01 '20 at 16:30

Gordon Linoff

1 Answers1