SubQuery in INNER JOIN (MySQL)

Question

I seem to have a problem with a subquery in an inner join which doesn't do, what I'd.

• Server: Localhost via UNIX socket
• Software: MySQL
• Software version: 5.5.32-nmm2-log - (Ubuntu)
• Database client version: libmysql - 5.5.32

There's an m:n table construct with 3 tables of which only 2 are relevant to the problem.

• The first one is a table that contains actors, identifier: caid.
• The second one is the cross-table connects actors to movies (actor id: caid, movie id: id)

When I create a full inner join like this:

SELECT count( * ) AS Count, lastname, firstname
FROM DVDPROFILER_dvd_common_actor
INNER JOIN DVDPROFILER_dvd_actor ON DVDPROFILER_dvd_common_actor.caid = DVDPROFILER_dvd_actor.caid
WHERE DVDPROFILER_dvd_actor.caid > 0
GROUP BY DVDPROFILER_dvd_actor.caid
ORDER BY Count DESC 

I get exactly what i'd expect: The top actors counted by the times he's credited in any movie, even if that's multiple times for multiple roles.

My goal is to extract the information of how many different movies an actor is profiled in and I thought - naive as I am - it should be as simple as this:

SELECT count( * ) AS Count, lastname, firstname
FROM DVDPROFILER_dvd_common_actor
INNER JOIN
  (SELECT caid
  FROM DVDPROFILER_dvd_actor
  GROUP BY id) AS DVDPROFILER_dvd_actor 
ON DVDPROFILER_dvd_common_actor.caid = DVDPROFILER_dvd_actor.caid
WHERE DVDPROFILER_dvd_actor.caid > 0
GROUP BY DVDPROFILER_dvd_actor.caid
ORDER BY Count DESC 

But the results where totally incorrect

So I checked the subquery with a selected actor

SELECT caid, id
FROM DVDPROFILER_dvd_actor
WHERE caid = 30801
GROUP BY id

And got exactly what I expected

So I toyed a bit and when I introduced a LIMIT clause into the subquery I suddenly got different yet never correct replies.

SELECT count( * ) AS Count, lastname, firstname
FROM DVDPROFILER_dvd_common_actor
INNER JOIN 
  (SELECT caid
  FROM DVDPROFILER_dvd_actor
  GROUP BY id
  LIMIT 0 , 50000) AS DVDPROFILER_dvd_actor 
ON DVDPROFILER_dvd_common_actor.caid = DVDPROFILER_dvd_actor.caid
WHERE DVDPROFILER_dvd_actor.caid > 0
GROUP BY DVDPROFILER_dvd_actor.caid
ORDER BY Count DESC 

With different LIMITs I got different results but at some point when I go over a certain limit, the result is exactly as it is without the limit - but equally wrong.

What am I overlooking here? :-(

Answer 1

could you try this? I guess COUNT(DISTINCT DVDPROFILER_dvd_actor.id) is helpful for you.

SELECT lastname, firstname, COUNT(DISTINCT DVDPROFILER_dvd_actor.id) AS Count
FROM DVDPROFILER_dvd_common_actor
INNER JOIN DVDPROFILER_dvd_actor ON DVDPROFILER_dvd_common_actor.caid = DVDPROFILER_dvd_actor.caid
WHERE DVDPROFILER_dvd_actor.caid > 0
GROUP BY lastname, firstname
ORDER BY Count DESC 

If not, we are very happy when you post your data and schema on http://www.sqlfiddle.com/. that makes us easier to test

Thanks.