Mysql Left Join Not working As Expected

Question

I am attempting a simple left join that is giving me problems. I need all the customers listed (table a) regardless of whether or not they have an invoice (table b) between a certain date range. Both of my attempts have yielded the only customer that has an invoice for that period:

select b.clientname,a.* from invdata a left join clidata b on a.clidataid=b.recordid where b.recstatus=1 and b.isactive=1 and a.reccreate between '2015-04-01 00:00:00' and '2015-04-30 23:59:59';

OR

select a.clientname,b.* from clidata a left join invdata b on a.recordid=b.clidataid where a.recstatus=1 and a.isactive=1 and b.reccreate between '2015-04-01 00:00:00' and '2015-04-30 23:59:59';

Little help, please. Thanks!!

Abhik Chakraborty · Accepted Answer · 2015-04-05 18:06:36Z

Left join with where condition is inner join and will filter the data as per the where condition, you may need to move the where condition into joining condition

 select b.clientname,a.* from invdata a left join clidata b on a.clidataid=b.recordid and b.recstatus=1 and b.isactive=1 and a.reccreate between '2015-04-01 00:00:00' and '2015-04-30 23:59:59';

Collectives™ on Stack Overflow

Mysql Left Join Not working As Expected

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related