> REQ3 is a problem because you have mixed signs in INV and only one record in > REP > > How would I craft a query to return REQ3?
Could be something like this: select INV.REQ, count(case when INV.AMT > 0 then 1 else null end) positive, count(case when INV.AMT < 0 then 1 else null end) negative, count(distinct REP.rowid) rep_amount from INV, REP where INV.REQ = REP.REQ group by INV.REQ having positive > 0 and negative > 0 and rep_amount = 1; Pavel On Tue, Apr 26, 2011 at 12:23 PM, David Bicking <dbic...@yahoo.com> wrote: > I have two tables: > > CREATE TABLE INV > ( REQ int, > INV char, > AMT float ); > > CREATE TABLE REP > ( REQ int, > ACCT char > AMT float ); > > I need to flag any combinations where for a given REQ value, one table has a > list of numbers of mixed signs, and the other table has one and only one > value. > > So > REQ|INV|AMT > 1|i1|1 > 1|i2|2 > 2|i3|3 > 2|i4|-6 > 3|i5|5 > 3|i6|-4 > > REQ|ACCT|AMT > 1|a1|3 > 2|a2|-2 > 2|a3|-1 > 3|a4|1 > > REQ1 is okay because the values are the same sign. > REQ2 is okay because there are more than one record in each table > REQ3 is a problem because you have mixed signs in INV and only one record in > REP > > How would I craft a query to return REQ3? > > At this point, I haven't a clue, so any help would be appreciated. > > Thanks, > David > > _______________________________________________ > 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
