SubQuery in INNER JOIN (MySQL)
inner-join, mysql, subquery
Solution
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.
Problem
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? :-(