problem with my query

[jerrylouise]

this errors out and i dont know why.

$sql = "SELECT s.`id`, s.`topic`, s.`username`, s.`date`, s.`subject`, s.`post` COUNT(c.`posts_id`) AS `ct` FROM `forum-posts` s LEFT JOIN `forum-reply` c  ON s.`subject` = c.`posts_id` WHERE `topic` = '$topic'";

[jerrylouise]

ok i fixed that with

$sql = "SELECT s.`id`, s.`topic`, s.`username`, s.`date`, s.`subject`, s.`post`, COUNT(c.`posts_id`) AS `ct` FROM `forum-posts` s LEFT JOIN `forum-reply` c  ON s.`subject` = c.`posts_id` WHERE s.`topic` = '$topic' GROUP BY s.`id`";

now i need to get the same thing working here.

$sql = "SELECT s.`id`, s.`catagory`, c.`title`, c.`about`, COUNT(d.`posts_id`) AS `ct` FROM `forum-posts` d LEFT JOIN `forum-catagory` s LEFT JOIN `forum-topics` c ON s.`id` = c.`catagory_id` ORDER BY s.`id`";

[alimadzi]

When you LEFT JOIN 'forum-category', you need to specify a join condition.

[jerrylouise]

made a right mess lol
[PHP[$sql = "SELECT s.id, s.catagory, c.title, c.about, COUNT(d.posts_id) AS ct FROM forum-posts s LEFT JOIN forum-reply d ON c.title = d.posts_id forum-catagory AS s LEFT JOIN forum-topics AS c ON s.id = c.catagory_id ORDER BY s.id";[/code]

[alimadzi]

So it's working now? If so, go ahead and mark the thread as resolved. Thanks.

[bradgrafelman]

Also, if you're having troubles with SQL queries, you should be pasting the errors returned from the SQL server for us.

[jerrylouise]

[error]
Query failed(SELECT s.id, s.catagory, c.title, c.about, COUNT(d.posts_id) AS ct FROM forum-posts s LEFT JOIN forum-reply d ON c.title = d.posts_id forum-catagory AS s LEFT JOIN forum-topics AS c ON s.id = c.catagory_id ORDER BY s.id): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'forum-catagoryASsLEFT JOINforum-topicsAScON s.id= c.catagory_i' at line 1
[/error]

[alimadzi]

Check your first join condition. You LEFT JOIN 'forum-reply' to 'forum-posts' but then use a column from 'forum-topics' in the condition even though that table hasn't been introduced yet.

[jerrylouise]

i see, but i cant seem to fix it. Been reading up on muiltipull table joining but its just confusing.

[alimadzi]

To make your query more readable, I suggest using meaningful table aliases. Some examples would be:

forum-posts AS fp
forum-reply AS fr
forum-category AS fc
forum-topics AS ft

Change those aliases and give it another shot.

[jerrylouise]

will do that tomorrow im to sleepie just now.