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 by_or_on desc)
on c.firm_id = f.id
order by boo desc;
Perhaps you can describe the result you are attempting to obtain.
**the having clause filters the "return rows" of a "group by" (aggregate)
select. Therefore the references in the HAVING can only test against "returned
columns", not against the "source columns" from which the result is derived.
Once uses the WHERE clause to apply conditions to the input of the aggregate,
and HAVING to apply conditions to the output.
**the same applies to the "order by" of a "group by" (aggregate) select. You
can only order by the "returned result columns", not by the "source data
columns".
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 boo desc;
of course, the order by inside the table subquery is useless, so this would
become:
Select f.*
from firms f
left join (select firm_id, max(by_or_on) as boo
from calls
group by firm_id
having boo is not null) c
on c.firm_id = f.id
order by boo desc;
but perhaps you really mean:
Select f.*
from firms f
left join (select firm_id, max(by_or_on) as boo
from calls
where by_or_on is not null
group by firm_id) c
on c.firm_id = f.id
order by boo desc;
which is a somewhat different thing.
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
> -----Original Message-----
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of e-mail mgbg25171
> Sent: Saturday, 02 February, 2013 16:19
> To: General Discussion of SQLite Database
> Subject: [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 WHY
> "order by by_or_on desc" & _
> ") c " & _
> "on c.firm_id = f.id " & _
> "order by boo desc;"
>
> Any help much appreciated
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
