Re: [sqlite] trying to exclude records which have a field that is null

I thought I tried where by_or_on is not null to start with and it didn't SEEM to work hence the <> ''. However...I've just replaced <> '' with IS NOT NULL and it works fine so I'm a bit mystified Thanks for the advice all the same though On 3 February 2013 10:08, Petite Abeille

Re: [sqlite] trying to exclude records which have a field that is null

On Feb 3, 2013, at 10:55 AM, e-mail mgbg25171 wrote: > (select * from calls where by_or_on <> '') c For the record… one thing to watch out… the empty string (aka '') and null are not the same… so if you are looking to eliminate nulls you have to use 'foo is not

Re: [sqlite] trying to exclude records which have a field that is null

I need to test this but this is looking promising select f.* from firms f inner join (select firm_id, max(by_or_on) from calls where by_or_on <> '' group by firm_id order by by_or_on) c on f.id = c.firm_id What do you think On 3 February 2013 09:55, e-mail mgbg25171

Re: [sqlite] trying to exclude records which have a field that is null

I've done most of it with this select f.* from firms f inner join (select * from calls where by_or_on <> '') c on f.id = c.firm_id Phew! On 3 February 2013 09:40, e-mail mgbg25171 wrote: > Keith, Petite > I'm really grateful for your assistance. I've tried your

Re: [sqlite] trying to exclude records which have a field that is null

Keith, Petite I'm really grateful for your assistance. I've tried your solutions and they don't quite give me what I want so... sorry for misleading you. Secondly your suggestion that I explain in words is a good one. Here goes... I've got a table of FIRMS and a table of CALLS made to those

Re: [sqlite] trying to exclude records which have a field that is null

> Mayhaps you mean: > > Select f.* >from firms f > left join (select firm_id, max(by_or_on) as boo > from calls > group by firm_id >having by_or_on is not null > order by by_or_on desc) c > on c.firm_id = f.id > order by

Re: [sqlite] trying to exclude records which have a field that is null

On Feb 3, 2013, at 12:19 AM, e-mail mgbg25171 wrote: > "having by_or_on is not null " & _<==THIS ISN'T DOING IT FOR ME > AND I'D LIKE TO KNOW WHY Use a where clause ___ sqlite-users mailing list

Re: [sqlite] trying to exclude records which have a field that is null

I'm surprised you are getting anything at all since the statement is semantically invalid. Select f.* from firms f left join (select firm_id, max(by_or_on) as boo from calls group by firm_id having by_or_on is not null order by

[sqlite] trying to exclude records which have a field that is null

wSQL = _ "Select f.* " & _ "from firms f " & _ "left join " & _ "(" & _ "select firm_id, max(by_or_on) as boo " & _ "from calls " & _ "group by firm_id " & _ "having by_or_on is not null " & _<==THIS ISN'T DOING IT FOR ME AND I'D LIKE TO KNOW