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 <mgbg25...@blueyonder.co.uk>wrote: > 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 firms. > In the table of calls to the firms, two of the fields are > unsurprisingly...FIRM_ID and > a a field called BY_OR_ON that records the date by which something must be > done. > BY_OR_ON is mostly null. > I want to FILTER the FIRMS records to EXCLUDE any which do not have a > single calls record with a non-null by field > I'm only interest in the LATEST/MAX BY_OR_ON date for each firm which will > exclude many of the calls records. > I then want to SORT the FIRMS records to order them by earlest...latest > BY_OR_ON date for each firm, first... > if earlies...latest BY_OR_ON makes sense. > > I hope this helps to explain > Best Regards > > > > > > On 3 February 2013 03:39, Keith Medcalf <kmedc...@dessus.com> wrote: > >> > 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; >> >> Should be: >> >> Select f.* >> from firms f >> left join (select firm_id, max(by_or_on) as boo >> from calls >> group by firm_id >> having max(by_or_on) is not null >> order by max(by_or_on) desc) c >> on c.firm_id = f.id >> order by boo desc; >> >> or the equivalent: >> >> 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 >> order by boo desc) c >> on c.firm_id = f.id >> order by boo desc; >> >> --- >> () 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 Keith Medcalf >> > Sent: Saturday, 02 February, 2013 17:11 >> > To: General Discussion of SQLite Database >> > Subject: 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 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 >> >> >> >> _______________________________________________ >> 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
