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 <mgbg25...@blueyonder.co.uk>wrote: > 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
