SQL JOIN

[gaza165]

can someone tell me how to join 3 tables into one sql query. this is what ive got so far.

 

SELECT blog.id , blog.short_description , blog.blog_img, blog.title, blog.blog_created,blog_tags.tagname
FROM blog
LEFT JOIN blog_tags
ON blog.id=blog_tags.blog_id
ORDER BY blog.blog_created DESC

 

i need to join another table onto it.

[flyhoney]

SELECT 
blog.id, 
blog.short_description, 
blog.blog_img, 
blog.title, 
blog.blog_created,
blog_tags.tagname
FROM 
blog
LEFT JOIN blog_tags ON blog.id = blog_tags.blog_id
LEFT JOIN other_table ON blog_tags.blog_id = other_table.blog_id
ORDER BY 
blog.blog_created DESC

[gaza165]

also the table that im trying to add, i wish to count all the records in it, then echo it out.

 

this is what ive got but it only brings back 1 record

 

SELECT
   blog.id,
   blog.short_description,
   blog.blog_img,
   blog.title,
   blog.blog_created,
   blog_tags.tagname,
   COUNT(blog_comments.comments) as count
FROM
   blog
   LEFT JOIN blog_tags ON blog.id = blog_tags.blog_id
   LEFT JOIN blog_comments ON blog_tags.blog_id = blog_comments.blog_id
ORDER BY
   blog.blog_created DESC

[flyhoney]

If you want to get all the comments for a specific blog post, you are going to need 2 queries.  It cannot be done with one.

 

<?php
function getBlogs() {
$blogs = array();
$query = "
	SELECT
		 blog.id,
		 blog.short_description,
		 blog.blog_img,
		 blog.title,
		 blog.blog_created,
	FROM
		 blog
	ORDER BY
		 blog.blog_created DESC
";
$result = mysql_query();
while ($row = mysql_fetch_assoc($result)) {
	$row['tags'] = getTagsByBlogId($row['id']);
	$row['comments'] = getCommentsByBlogId($row['id']);
	$blogs[] = $row;
}
return $blogs;
}

function getTagsByBlogId($blog_id) {
$tags = array();
$query = "
	SELECT
		*
	FROM
		blog_tags
	WHERE
		blog_id = " . mysql_real_escape_string($blog_id) . "
";
$result = mysql_query();
while ($row = mysql_fetch_assoc($result)) {
	$tags[] = $row;
}
return $tags;
}

function getCommentsByBlogId($blog_id) {
$comments = array();
$query = "
	SELECT
		*
	FROM
		blog_comments
	WHERE
		blog_id = " . mysql_real_escape_string($blog_id) . "
";
$result = mysql_query();
while ($row = mysql_fetch_assoc($result)) {
	$comments[] = $row;
}
return $comments;
}

$blogs = getBlogs();

?>