[SOLVED] Join only returning one item

[mikwit]

I have two tables, with a join/count command someone suggested to me earlier but even with no limit or a limit like below, it only returns one item.  Any suggestions?

SELECT wlu_main . * , COUNT( comments.replyto ) AS comment_count
FROM wlu_main
LEFT JOIN comments ON wlu_main.number = comments.replyto
GROUP BY replyto
LIMIT 0 , 30

[fenway]

Hard to believe... you you mean the count or the record count?

[kickstart]

Hi

 

With a GROUP BY you should have all the columns that are not in the SUM / MAX / COUNT. If you any missing then the results in the column are not guaranteed.

 

You seem to be trying to group by the field that you are counting, while not even returning the field that you are counting.

 

What are the table structures and what exactly are you trying to count?

 

All the best

 

Keith

[kickstart]

Hi

 

I think this will give you what you want

 

SELECT boardId, COUNT( postId ) , SUM( commentsCount )
FROM (SELECT board.id AS boardId, posts.id AS postId, COUNT( comments.id ) AS commentsCount
FROM board
LEFT OUTER JOIN posts ON board.id = posts.board_id
LEFT OUTER JOIN comments ON posts.id = comments.post_id
GROUP BY board.id, posts.id)Deriv1
GROUP BY boardId 

 

All the best

 

Keith

[mikwit]

I don't really see what your getting at kick start.  I don't think I explained the problem well enough, I just assumed that there was a little thing wrong with my original request.  So heres some information, the database is  MYSQL 5.0 (I don't see any more information about subversions). 

 

I have two tables, one with posts (`wlu_main`) and one with comments (`comments`) that are for the posts.  The posts table is set up with an unique id number (`number`), as well as the school number which the post is in respect to (`school`).  The comments section is set up with a field `replyto` which represents which post the comment belongs to. 

 

I would like a query that is able to grab all (or part) of the `wlu_main` table and append a column that contains the count of rows in comments that have `comments.replyto` = `wlu_main.number`.

 

I can include a diagram if this is confusing... The query above is only returning one result even though there are some 20 entries in the wlu_main table.  Thanks so so much, I know I could have done better explaining this initially and thanks for staying with me on this. 

[kickstart]

Hi

 

Think where we might be getting confused is I based my reply on your last thread, which seemed to be where you got your non working suggestion.

 

Might be best if you can post the full table declares, and a couple of examples / what you expect to get out.

 

All the best

 

Keith

[mikwit]

Heres the SHOW MAKE TABLE for both tables in question I added the comments after to explain:

Generated by: phpMyAdmin 2.6.4-pl3 / MySQL 5.0.81-log
SQL query: SHOW CREATE TABLE wlu_main;
CREATE TABLE `wlu_main` (
 `number` int(11) NOT NULL auto_increment, \\ id number of the post
 `school` smallint(6) NOT NULL, \\ id number of the board where the post belongs
 `date` datetime NOT NULL, \\time the post was submitted
 `text` text collate latin1_german2_ci NOT NULL, \\text of the post
 `category` tinyint(4) NOT NULL, \\ currently not being implemented
 `likes` int(11) NOT NULL,  \\ voting system 
 `dislikes` int(11) NOT NULL, \\ voting system 
 `md5` varchar(32) collate latin1_german2_ci NOT NULL, \\password to remove
 `banned` tinyint(1) NOT NULL, \\ currently not being implemented
 PRIMARY KEY  (`number`),
 FULLTEXT KEY `text` (`text`)
) ENGINE=MyISAM AUTO_INCREMENT=32 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci PACK_KEYS=0

CREATE TABLE `comments` (
 `replyto` int(11) NOT NULL, \\ the id of the post which the comment is a reply to
 `date` datetime NOT NULL, \\ the time the comment was submitted
 `text` tinytext collate latin1_german2_ci NOT NULL, \\ the text of the comment
 KEY `replyto` (`replyto`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci

 

The result I'm trying to achieve is to list all the entries in wlu_main so that all the fields of wlu_main are there along with a field that contains the amount of comments for that particular entry (comment_count as below)

[kickstart]

Hi

 

That should be pretty easy.

 

Try this

 

SELECT `number`,`school`,`date`,`text`,`category`,`likes`,`dislikes`,`md5`,`banned`, b.CommentCount
FROM wlu_main a
LEFT OUTER JOIN (SELECT replyto, COUNT(`text`) AS CommentCount FROM `comments GROUP BY replyto) b
ON `number` = b.replyto

 

I have done a join against a subselect to avoid having a confusing GROUP BY with loads of columns.

 

The original suggestion was more complex as it was aimed at giving counts from 2 joined tables.

 

All the best

 

Keith

[mikwit]

Dude, thanks so much, it works! I know this is a silly question but is there anyway to make the new column be 0 instead of NULL when count is 0 or will i just have to hammer that out with some php magic?

[kickstart]

Hi

 

You could modify it like this:-

 

SELECT `number`,`school`,`date`,`text`,`category`,`likes`,`dislikes`,`md5`,`banned`, IFNULL(b.CommentCount,0)

FROM wlu_main a

LEFT OUTER JOIN (SELECT replyto, COUNT(`text`) AS CommentCount FROM `comments GROUP BY replyto) b

ON `number` = b.replyto

 

All the best

 

Keith

[mikwit]

For some reason that's returning a column titled IFNULL(b.CommentCount,0), but it does contain 0 instead of null, however I just left it with null and did a little php if statement to work around it.

[kickstart]

Hi

 

You can give it an alias name, something like IFNULL(b.CommentCount,0) AS CountOfComments

 

All the best

 

Keith