Thanks for the suggestion but unfortunately this seems to produce the product of the two tables `lghyperlink` and `lgsearch`. e.g. if user bill and 10 entries in `lghyperlinks` and 15 entries in `lgsearch` then what is returned is
username clicks searches ========================== bill 150 150 instead of username clicks searches ========================== bill 10 15 Any other suggestions gratefully received - maybe this has to be done in two queries and the data manipulated in the application: less than ideal but if there is no other way... best Bill -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 21 January 2004 16:51 To: [EMAIL PROTECTED] Cc: compuserve Subject: Re: select count from three tables * 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]
