Querying Two Tables :) 1

[kennygadams]

Hi,

I am developing the following page which will list the top 10 terms used to describe an image at ClipArtOf:

http://www.clipartof.com/most-popular

I need to select data from the following tables...

TABLE: term (has 40,000+ unique records)
FIELDS: term_id | term

TABLE: image_number (has 800,000+ duplicate records)
FIELDS: term_id | image_number | rank

Here is the code that I have so far and it is returning the top ten term_id's from the image_number table but I need it to also return "term" from the "term" table:

SELECT term_id, COUNT(*) AS daCount FROM image_number GROUP BY term_id HAVING daCount > 1 ORDER BY daCount DESC LIMIT 10

Thanks,

Kenny G. Adams

 

[r937]

in this case you probably don't need to use the HAVING clause ;-)

SELECT term.term
     , counts.daCount
  FROM term
INNER
  JOIN ( SELECT term_id
              , COUNT(*) AS daCount 
           FROM image_number 
         GROUP 
             BY term_id ) AS counts
    ON counts.term_id = term.term_id
ORDER 
    BY counts.daCount DESC LIMIT 10

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[kennygadams]

That worked like a charm! And it's fast too

Normally I would loop through the first select statement and do the other select statement inside the loop.

Thanks again!

Kenny G. Adams