Any way to do this w/o using two separate queries?

Question

I have an HTPC.  It uses a MySQL back end to store the info about TV shows and stuff.  One table is "info" which has synopsis type stuff and the other table is "media" which has info on each individual file/episode.  It relates by info.id = media.parent.  I have a report I run called "Series that may have ended" which tells me a list of shows with 13, 26 or 51 episodes.  Right now I do something like:

SELECT path FROM info

while (reading) {

  SELECT COUNT(fileid) AS reccnt FROM media WHERE parent = (info.id)

  if db(reccnt) matches, display record

}

That works, but it's slow.  I was wondering if there is a way to have the server return the list all at once rather than nesting another SELECT....?

Answer 1

I am not familiar with MySQL but using T-SQL  it could look like 

SELECT COUNT(fileid) AS reccnt ,path  FROM media JOIN  info ON  media.parent =info.id

GROUP BY path 

OR 

SELECT path,reccnt    FROM info 

JOIN (SELECT COUNT(fileid) AS reccnt,parent   FROM media GROUP BY parent) AS Der

ON Der.parent =info.id

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Ahh thanks a lot.  That was exactly what I needed - way faster too!  :)

Doh, for some reason I thought "transact sql" was like the core of all SQLs, oops. 

Answer 3

please mark as answer

---

Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com