WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = *current_row_primary_key*) > 150

[MaxMumford]

Hi all,

I need to do a query like this:

SELECT * FROM table1 WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = *current primaryKey*) > 150

How do I get the *current primaryKey* in order to do the second section of the query?

Max.

[darkagn]

Assuming the primary key is stored in a PHP variable, $key, then you can build your SQL query using double quotes:

$sql = "SELECT * FROM table1 WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = $key) > 150";
// run the query by passing $sql to your query handler

[MaxMumford]

I dont think I can explain it easily.

I basically want to encapsulate this:

$get_photos = mysql_query('SELECT * FROM images');//get all photos
while($photos = mysql_fetch_array($get_photos) )
{
     $get_ratings = mysql_query('SELECT COUNT(fbid) FROM ratings WHERE fbid = "'.$photos['fbid'].'" ');
     $ratings = mysql_fetch_array($get_ratings);
     if($ratings['COUNT(fbid)'] > 150){//if they have more than 150 ratings
          echo 'We can use this photo';//we want to use it
     }
     else{
          continue;//otherwise ignore it
     }
}

into a single mysql query...

[MaxMumford]

or is it not possible?

[nav33n]

What does your query do ? Query the table 'photos' and for every photo, You are again querying the table 'ratings' if that photo's fbid has a count > 150 ?
I didn't get the question or what you actually want. Do these 2 tables have any common 'linking' field ? What is fbid ?

[MaxMumford]

each photo is saved in the table images with FBID as the primary key.

Then ratings are saved in the ratings table and FBID refers to the photo the rating is about.

I want to only use photos that have at least 150 ratings about them saved in the ratings table.

hope thats clearer

[nav33n]

select * from images where fbid IN (select fbid from ratings HAVING count(fbid) > 150 group by fbid)

The subquery returns fbid of all the records which has a count of 150+ . The main query returns all the records which matches these fbid.
Read more about having clause here.. http://www.techonthenet.com/sql/having.php

Not sure if this will work. If it doesn't, we ll try some other way :)
Cheers!

[MaxMumford]

Heyy,

Thanks for the reply, your answer makes sense and seems very logical. I entered the following code in the phpmyadmin code tester:

SELECT * FROM images WHERE fbid IN (SELECT fbid FROM ratings HAVING count(fbid) > 150 GROUP BY fbid)

and got the following error:

#1064 - 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 'GROUP BY fbid) LIMIT 0, 30' at line 1

Thanks again

[nav33n]

Hmm.. There was a little mistake in that query. Here, this works fine.

select * from images where fbid IN (
select fbid
from rating
group by fbid
HAVING count( fbid ) > 150
)

Cheers!

[MaxMumford]

perfect, thanks a lot :D

[nav33n]

:) You are welcome!