On Mon, Aug 04, 2008 at 05:00:31PM -0400, Rajarshi Guha wrote: > On Aug 4, 2008, at 4:55 PM, Francisco Reyes wrote: > > select a.cid as ac, b.cid as bc, count(*) from aic_cid a left > >outer join > >aic_cid b on a.cid <>b.cid and a.id = b.id where b.cid is not null > >group by > >a.cid, b.cid order by a.cid; > > > >Is that what you are looking for? > > Thanks a lot - this is very close. Ideally, I'd want unique pairs You just need to change the "a.cid <> b.cid" equality to something non-symmetric, i.e. "a.cid < b.cid". I'm also not sure why an outer join is being used. I've rewritten it to: SELECT a.cid AS ac, b.cid AS bc, count(*) FROM aic_cid a, aic_cid b WHERE a.id = b.id AND a.cid < b.cid GROUP BY a.cid, b.cid ORDER BY a.cid, b.cid; and seem to get similar results. Sam
