to JOIN or not to JOIN

der_metzger

i try to join three MySQL querys but how can i do this?
the querys are like the following:

SELECT user_id, nic, lastlogin FROM user LIMIT 0,9

SELECT count(guestbook.id) AS guestposts FROM guestbook WHERE user= ('user_id' from the first querry)

SELECT count(forum_posts.id) AS forumposts FROM forum_posts WHERE user= ('user_id' from the first querry)

i have no idea how to join the three query's
all my trys end in error messages...

bogu

SELECT u.user_id,u. nic,u. lastlogin,count(g.guestbook.id) AS guestposts,count(f.forum_posts.id) AS forumposts FROM user u 
LEFT JOIN guestbook g USING (user_id)
LEFT JOIN forum_posts f USING (user_id) 
WHERE u.user_id=$UserId GROUP BY u.user_id

der_metzger

Originally posted by bogu

SELECT u.user_id,u. nic,u. lastlogin,count(g.guestbook.id) AS guestposts,count(f.forum_posts.id) AS forumposts FROM user u 
LEFT JOIN guestbook g USING (user_id)
LEFT JOIN forum_posts f USING (user_id) 
WHERE u.user_id=$UserId GROUP BY u.user_id

[/B]

my try is

SELECT u.user_id, u.nic, u.lastlogin,
 count(g.angst_guestbook.id) AS guestposts,
 count(f.angst_forum_posts.posttime) AS forumposts FROM angst_user u 
 LEFT JOIN angst_guestbook g USING (user_id)
 LEFT JOIN angst_forum_posts f USING (user_id) 
 GROUP BY u.user_id

and the following error-message is:
Unknown table 'g.angst_guestbook' in field list

but i dont know why...
the table angst_guestbook is there...


shortend Table structure:
CREATE TABLE angst_guestbook (
  id int(11) NOT NULL auto_increment,
  user_id int(11) NOT NULL default '0',
  posttime timestamp(14) NOT NULL,
  message text NOT NULL,
  visitor mediumint(9) NOT NULL default '0',
  PRIMARY KEY  (id),
) TYPE=MyISAM;
CREATE TABLE angst_forum_posts (
  user_id mediumint(9) unsigned NOT NULL default '0',
  posttime timestamp(14) NOT NULL,
  message text NOT NULL,
  PRIMARY KEY  (post_id),
) TYPE=MyISAM;
CREATE TABLE angst_user (
  user_id int(9) unsigned NOT NULL auto_increment,
  nic varchar(22) NOT NULL default '',
  reg_mail text NOT NULL,
  register date NOT NULL default '0000-00-00',
  lastlogin timestamp(12) NOT NULL,
  PRIMARY KEY  (user_id),
) TYPE=MyISAM PACK_KEYS=1 CHECKSUM=1;

bogu

SELECT u.user_id,u. nic,u. lastlogin,count(g.id) AS guestposts,count(f.post_id) AS forumposts FROM angst_user u 
LEFT JOIN angst_guestbook g USING (user_id)
LEFT JOIN angst_forum_posts f USING (user_id) 
WHERE u.user_id=$UserId GROUP BY u.user_id

And you have a problem at your angst_forum_posts table because you define your primery key but is not created and hope that problem is only in structure that you gave me.
Var $UserId should be the user_id. (ex. $UserId=5😉

der_metzger

and...
if there is a second posting in angst_forum_posts
the result is 502... MySQL multiply the forum and the guestbook post result... 🙁 but why?

bogu

What exactly did you meen by "MySQL multiply the forum and the guestbook post result".

der_metzger

MySQL did the folowing:

there are 1 forumposts and 251 guestposts,
MySQL tells me:
251 forumposts and 251 guestposts
(2511) (2511)

there are 2 forumposts and 251 guestposts,
MySQL tells me:
502 forumposts and 502 guestposts
(2512) (2512)

and so on... and Why???

bogu

I try all I know about this SELECT and the only ideea to make it work is by doing to selection one for count from angst_guestbook
and the second one is for count from angst_forum_posts:

SELECT u.user_id,u.nic,u.lastlogin,count(g.id) as nrg 
FROM angst_user u 
LEFT JOIN angst_guestbook g USING(user_id) 
WHERE u.user_id=2 GROUP BY user_id

SELECT u.user_id,count(f.post_id) as nrf 
FROM angst_user u 
LEFT JOIN angst_forum_posts f USING(user_id) 
WHERE u.user_id=2 GROUP BY user_id

If you find out how to make it work with one select please send me an email.

der_metzger

yes, but the problem is, i want an ORDER BY to sort my members by guestbookposts or by forumposts 🙁
there is no way i can find to do that...