Re: REPOST: Trouble with SQL conversion

Ok, did that - now it works but returns no records.

What I am after is to reduce the right hand table to one row matching
the left table. There may be no, one or many matching records in the
right table, but I want to group them on the key and find out the number
of records in the right table at the same time.

This works just fine in that other database :-)

>>> Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> 2002/04/11
03:28:32 >>>
> SELECT base.baseindex, base.subnetsize, base.descrip, base.baseaddr,
> base.admingrp, count(ipaddr.baseindex) AS cnt FROM base
> LEFT JOIN ipaddr ON base.baseindex=ipaddr.baseindex WHERE
> base.baseaddr
> BETWEEN 2473473024 AND 2473473151 AND base.customer=1
> GROUP BY base.baseindex
> ORDER BY base.baseaddr;

> 0: ERROR: Attribute base.subnetsize must be GROUPed or used in an
> aggregate function

Do what it says. Add base.subnetsize to the GROUP BY. You'll
probably
need to add base.descrip, base.baseaddr and base.admingrp as well.

Chris