This seems to have answered part of my problem SELECT id FROM firms f left outer join (select firm_id from calls group by firm_id) c on f.id = c.firm_id order by firm_id
On 4 February 2013 07:40, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk>wrote: > Igor > Thank you! > Your query is extremely close > but for the repetition of the c.ids at the end > > I've tried putting group by (c,id) but got an error > Indeed my weakness seems to be having little idea of how to insert the > stuff for a single query into compound queries. > e.g. > If I want to introduce an "order by clause" for the firms stuff I can't > quite see how to insert it without again causing an error. > > Any advice greatfully received. > Dean > > > > On 4 February 2013 03:40, Igor Tandetnik <i...@tandetnik.org> wrote: > >> On 2/3/2013 3:43 PM, e-mail mgbg25171 wrote: >> >>> SELECT f.id FROM firms AS f >>> WHERE f.id NOT IN (SELECT c1.firm_id FROM calls AS c1) OR f.id IS NULL >>> union >>> SELECT f2.id FROM firms AS f2 >>> WHERE f2.id IN (SELECT c2.firm_id FROM calls AS c2) >>> order by (c1.last is null, c2.last is not null) >>> >>> I have two tables firms and calls. >>> I'd like to list all the firms for which no calls records exist >>> then >>> I'd like to list all firms for which calls records DO exist >>> in order of earliest last call first >>> >> >> select f.id from firms f left join calls c on (f.id = c.firm_id) >> order by c.last; >> >> NULL compares less than any other value, so rows for which there are no >> calls will sort at the top. >> -- >> Igor Tandetnik >> >> ______________________________**_________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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
