Quoth Drake Wilson <dr...@begriffli.ch>, on 2010-10-05 03:24:01 -0700: > > My current task is to get the number of foods that belong to each > > group and have at least one weight data related to them. > > That suggests something like: > > SELECT g.Z_PK AS "group", COUNT(f.Z_PK) AS "count" > FROM ZFFFOODGROUP g > INNER JOIN ZFFFOOD f ON f.ZFOODGROUP = g.Z_PK > WHERE EXISTS (SELECT w.Z_PK FROM ZFFWEIGHT w WHERE w.ZFOOD = f.Z_PK) > GROUP BY g.Z_PK;
Oh---actually I may have misinterpreted your semantics slightly. This will only find groups that have at least one such row. If you want to find groups with no such foods and get an explicit zero as a result, you would have to change that to a LEFT JOIN and then move the EXISTS somewhere else to ensure at least one row for the zero-result groups. I'd probably drop the WHERE, then replace the COUNT with SUM(EXISTS (SELECT ...)) because the EXISTS will return 1 for rows where the subselect returns non-empty, though there might be a better way to do that. ---> Drake Wilson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
