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" <[EMAIL PROTECTED]>
To: "Eric L. Blevins" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
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 [EMAIL PROTECTED]
> 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
>
>
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
