0

I am doing a left outer join : 

select * from tableA
left join tableB on tableA.tableB_id = tableB.id

How can I add another search criteria on tableB only for matching tableB records? (like 'and tableB.firstname like 'a_firstname%') If I do : 

select * from tableA
left join tableB on tableA.tableB_id = tableB.id
where tableB.firstname like 'a_firstname%'

it doesn't display records where on tableA.tableB_id = tableB.id doesn't match.

thomas
  • 1,201
  • 2
  • 15
  • 35
  • 4
    Move the condition to `ON` clause. `..on tableA.tableB_id = tableB.id and tableB.firstname like 'a_firstname%'` – Pரதீப் Jun 29 '17 at 08:19
  • Also start using meaningful `Alias` names to make the query more readable – Pரதீப் Jun 29 '17 at 08:20
  • "it doesn't display records where on tableA.tableB_id = tableB.id doesn't match." Most probably you need `OR`. Show sample data and desired result please. – Serg Jun 29 '17 at 08:24
  • Please show sample data and expected resault – Ilyes Jun 29 '17 at 08:25
  • 2
    @Serg - Non matching records will have `NULL` values in `tableB.firstname` which will be filtered by the `tableB.firstname like 'a_firstname%'` . Implicitly it will be converted to `INNER JOIN`. So the condition should be moved to `ON` clause which tells what are the records to be joined with `tableA` instead of filtering the result – Pரதீப் Jun 29 '17 at 08:26
  • @Prdp Yes, apparently it should be moved to `ON`, but probably `OR`ed as well. or may be not, it's not clear without an example. – Serg Jun 29 '17 at 08:32
  • Possible duplicate of [SQL Different between Left join on... and Left Join on..where](https://stackoverflow.com/questions/44696051/sql-different-between-left-join-on-and-left-join-on-where) – Zohar Peled Jun 29 '17 at 08:50
  • Moving to ON clause was the solution. Thanks. – thomas Jun 29 '17 at 09:30

1 Answers1

0

please check this 

 select * from tableA
    left join tableB on tableA.tableB_id = tableB.id and tableB.firstname like 'a_firstname%'
Ravi
  • 1,157
  • 1
  • 9
  • 19