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
