* compuserve aka Bill Stennett
> I have the following situation:
>
> the DB has three tables 'users', 'links' and 'searches'. Each table has a
> common key named 'userid'
This does not match the table/column names you describe below... are you
trying to confuse us? ;)
> What I want to do is, for each user in the 'lguser' table I'd
> like to count
> the number of corresponding records in EACH of the 'lghyperlink' and
> 'lgsearch' tables.
>
> I have the following query which counts the number of records in
> `lghypoerlink` for each record in `lguser` but I can't figure out how to
> incorporate the `lgsearch` table and count the rows.
>
> SELECT u . username , count( l.username ) AS clicks
> FROM `lguser` AS u
> LEFT JOIN `lghyperlink` AS l ON u.username = l.username
> GROUP BY u.username
> ORDER BY clicks DESC
>
> I'm trying for output like:
>
> username | clicks | searches
> ============================
> test | 12 | 45
> anon | 20 | 23
(This seems to be ordered by clicks ASC or searches DESC...?)
Have you tried something like this:
SELECT u.username,
count(l.username) AS clicks,
count(s.username) AS searches
FROM `lguser` AS u
LEFT JOIN `lghyperlink` AS l ON u.username = l.username
LEFT JOIN `lgsearch` AS s ON u.username = s.username
GROUP BY u.username
ORDER BY clicks DESC
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
