Need Help on Select and Sub Query in MySQL..

ex..
I have 2 tables pictures and comments

table : pictures
pid picture

table : comments
pid comments

sample data on picture
pid picture
1 mypicture1.jpg
2 mypicture2.jpg

sample data on comments
pid comments
1 comments1
1 comments2
1 comments3
2 comments1

how to display the data in pictures and no. of comments..??

sample
pid picture comments
1 mypicture1.jpg 3
2 mypicture2.jpg 1



I tried doing like this

SELECT a.pid, a.picture,
( select count(*) from b where a.pid = b.pid) as comments
FROM pictures a, comments b
WHERE a.pid = b.pid

But it doesnt work... Please need your help..Im using mysql 4.0

Thanks very much

 

Pre-4.1 versions do not support nestled SELECT statemements, sorry. Ontop of that, what does your second select do anyways? You don't check that count(*) against anything. Plus, obviously, the ending bracket is missing. Did you omit a part of your original query? Anyways, see if you can upgrade to at least 4.1 if you absolutely need the nestled SELECTs.

 

In other website it works....I see that's why.....

Get the total number of comments for each picture

I cannot upgrade thats pre installed on the web host...

Actually, I can do this with 2 queries.. but I would like to make it 1 for performance purposes...

 

Two queries would work fine, maybe even better/faster.

 

SELECT a.pid, picture, count(
COMMENTs )
FROM pictures a , comments b
WHERE a.pid = b.pid
GROUP BY a.pid

Code (markup):

 

SELECT 
    p.pid, p.picture, count(*)
FROM pictures p 
    INNER JOIN comments c ON p.pid = c.pid
GROUP BY 
    p.pid

Code (sql):

Similar to rosytoes, but explicitly stating the joins and in color

 

Both of Them Worked!!!! yyyeeeeaaaaahhhhooooooo!!!!!

Thanks rosytoes , dct !!! And to all who posted to this thread...

Now, which one is faster?? hehehehehe..

Again, many thanks!!!

 

You should add a new field to "pictures" table. You need "num_comments" or something like that so you don't have to count comments on every page load.

 

yes..actually im thinking about that... Im creating my own script for coppermine photo gallery to integrate into my forum.....I dont want to use other scripts(cpg_fetch)..its hard to customize...according to your needs..

thanks sokickit