Page 1 of 1

mySQL relational/join query

Posted: Thu Aug 30, 2007 3:23 am
by stakes
Hi everyone.

My mysql skills aren't really ace and im trying to solve this problem through one query not sure if im doing the right approach here.

There are three tables

BLOG

POSTS <-- contains column blog_id

PICTURES <-- contains column post_id

Now i want to perform a count on PICTURES that belong to POSTS that belong to BLOG. So something in the lines of.

"SELECT count(pictures.id)"."FROM pictures, posts"."WHERE pictures.post_id = $post_id AND posts.blog_id = $blog_id"

above is just guesswork but i assume it doesnt work :/

thanks for any help

/Daniel

Posted: Thu Aug 30, 2007 7:28 am
by Ind007
[s]U[/s] you should have a common column between these two tables, I assume that is post_id

select count(pictures) from pictures where post_id=ANY(select post_id from post where post_id='$postid' and blog_id='$blogid');
[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:11. Please use proper, complete spelling when posting in the forums. AOL Speak, leet speak and other abbreviated wording can confuse those that are trying to help you (or those that you are trying to help). Please keep in mind that there are many people from many countries that use our forums to read, post and learn. They do not always speak English as well as some of us, nor do they know these aberrant abbreviations. Therefore, use as few abbreviations as possible, especially when using such simple words.

Some examples of what not to do are ne1, any1 (anyone); u (you); ur (your or you're); 2 (to too); prolly (probably); afaik (as far as I know); etc.

Posted: Thu Aug 30, 2007 8:16 am
by stakes
Thanks Ind007 for your reply. I made use of mysql ANY however this is more what i wanted to do

Code: Select all

SELECT COUNT(id) FROM pictures WHERE post_id=ANY(select post_id FROM post WHERE blog_id='$blog_id')
This query seemed logical to me, but instead it counts ALL the pictures from all the blogs. What did i miss here?

Posted: Thu Aug 30, 2007 7:34 pm
by califdon
I had to actually look up the ANY() function, I don't think it's a very commonly used function. That's not what you want for this purpose. You were on the right track with your original SQL -- it might even almost work as is. You don't need all those concatenations, but you definitely must provide a space between keywords, which your example didn't.

What do you want to do, get a count of pictures for one particular post from one particular blog, or do you want to get a list of how many pictures are in each post from each blog?

I'd try something like this, to get just a single number:

Code: Select all

$sql = "SELECT count(pictures.id) FROM pictures, posts WHERE pictures.post_id = $post_id AND posts.blog_id = $blog_id";
Actually, if your post_ids are unique across different blogs, you wouldn't even need to do a join at all--just SELECT the count FROM pictures WHERE the post_id = some value.

Posted: Fri Aug 31, 2007 4:00 am
by stakes
califdon wrote:I had to actually look up the ANY() function, I don't think it's a very commonly used function. That's not what you want for this purpose. You were on the right track with your original SQL -- it might even almost work as is. You don't need all those concatenations, but you definitely must provide a space between keywords, which your example didn't.

What do you want to do, get a count of pictures for one particular post from one particular blog, or do you want to get a list of how many pictures are in each post from each blog?

I'd try something like this, to get just a single number:

Code: Select all

$sql = "SELECT count(pictures.id) FROM pictures, posts WHERE pictures.post_id = $post_id AND posts.blog_id = $blog_id";
Actually, if your post_ids are unique across different blogs, you wouldn't even need to do a join at all--just SELECT the count FROM pictures WHERE the post_id = some value.
I want to get a count of all the pictures of one particular blog. But the picture table is only related to the post table, while the post table is related to the blog table, so im trying to skip having to add a blog_id to the pictures table. So again, something like:

Code: Select all

$sql = "SELECT count(pictures.id) FROM pictures, posts WHERE posts.blog_id = '$blog_id'"
I tried this query. But it counted all the pictures twice, from the whole table. How can this be?

Posted: Fri Aug 31, 2007 10:08 pm
by califdon
stakes wrote:
califdon wrote:I'd try something like this, to get just a single number:

Code: Select all

$sql = "SELECT count(pictures.id) FROM pictures, posts WHERE pictures.post_id = $post_id AND posts.blog_id = $blog_id";
I want to get a count of all the pictures of one particular blog. But the picture table is only related to the post table, while the post table is related to the blog table, so im trying to skip having to add a blog_id to the pictures table. So again, something like:

Code: Select all

$sql = "SELECT count(pictures.id) FROM pictures, posts WHERE posts.blog_id = '$blog_id'"
I tried this query. But it counted all the pictures twice, from the whole table. How can this be?
That's right, the picture table is only related to the post table, but the post table (you told us) has the blog_id in it. Did you try the query I suggested? It looks to me like it should work. And yes, I'm not surprised that you got an inflated count with your query. Stop and think of what records would match your WHERE clause. You didn't put in any condition for the pictures table.