Message-ID: <00c301c27557$2c2cc010$0201a8c0@Gateway>
From: "Eric L. Blevins" <eblevins@insight.rr.com>
To: "Oliver Elphick" <olly@lfix.co.uk>
Cc: <pgsql-sql@postgresql.org>
References: <009c01c27541$83bf46b0$0201a8c0@Gateway>
	<1034800739.22821.105.camel@linda>
Subject: Re: Help with SQL
Date: Wed, 16 Oct 2002 17:01:12 -0400
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

This is what I ended up with:

SELECT c1.uid, count1, count2
FROM (SELECT uid, count(uid) AS count1
FROM triangulated WHERE uid != 'anonymus'
AND uid != 'anonymous'
AND uid != '' GROUP BY uid) AS c1
LEFT JOIN (SELECT uid,count(uid) AS count2
FROM points
WHERE uid != 'anonymus'
AND uid != 'anonymous'
AND uid != '' GROUP BY uid) as c2 on (c1.uid = c2.uid)
ORDER BY count1 DESC LIMIT 10;

I got the results I wanted!
     uid     | count1 | count2
-------------+--------+--------
 eblevins    |   1179 |  23595
 DaClyde     |    398 |  11031
 Drew        |     30 |    104
 zombiechick |      3 |    159
(4 rows)

Thanks for your help!

Eric L. Blevins

----- Original Message -----
From: "Oliver Elphick" <olly@lfix.co.uk>
To: "Eric L. Blevins" <eblevins@insight.rr.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Wednesday, October 16, 2002 4:38 PM
Subject: Re: [SQL] Help with SQL


> On Wed, 2002-10-16 at 19:26, Eric L. Blevins wrote:
> > I've got 2 SQL statements I would like to combine into one.
> ...
> > statement 1: SELECT  uid, count(uid)  FROM triangulated WHERE uid !=
'anonymus' AND uid
> > != 'anonymous' AND uid != '' GROUP BY uid ORDER BY count DESC LIMIT 10;
> ...
> > statement 2: SELECT  uid, count(uid)  FROM points WHERE uid !=
'anonymus' AND uid !=
> > 'anonymous' AND uid != '' GROUP BY uid ORDER BY count DESC LIMIT 10;
> ...
> > what I want to do is have one statement that returns something like
this:
> > uid    |    count1    | count2
> > eblevins    1179    23595
> > DaClyde    398    11031
> > Drew    30    104
> > zombiechick    3    159
> >
> > So everything is ordered like statement 1 but includes the count(uid)
from the points DB like statement 2 returns
>
> SELECT * FROM
>    (SELECT uid, count(uid) AS count1
>       FROM triangulated
>      WHERE uid != 'anonymus' AND
>            uid != 'anonymous' AND
>            uid != ''
>      GROUP BY uid) AS c1
>    LEFT JOIN
>    (SELECT uid, count(uid) AS count2
>       FROM points
>      WHERE uid != 'anonymus' AND
>            uid != 'anonymous' AND
>            uid != ''
>      GROUP BY uid) AS c2
>   ORDER BY count1 DESC
>   LIMIT 10;
>
> (Apologies for syntax errors, if any - I haven't tried it out,)
>
> I used LEFT JOIN because you are ordering by count1, so you probably
> won't want any rows where count1 is null.  If the total of rows from
> subselect c1 was likely to be less than 10, you might want to do a FULL
> JOIN and order by count1, count2.
>
> --
> Oliver Elphick                                Oliver.Elphick@lfix.co.uk
> Isle of Wight, UK
> http://www.lfix.co.uk/oliver
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
>                  ========================================
>      "But be ye doers of the word, and not hearers only,
>       deceiving your own selves."              James 1:22
>
>