[thelist] mysql join and subqueries

Matt Warden mwarden at gmail.com
Fri Jun 3 22:10:07 CDT 2005


On 6/3/05, partyarmy <partyarmy at gmail.com> wrote:
> having trouble with a complicated (for me) sql statement.
> SELECT members.member_id
> FROM members
> LEFT JOIN profile
> ON members.member_id=profile.member_id
> WHERE members.city = 'vancouver' AND profile.category_id = '0' AND
> profile.profile_data LIKE '%three%'
> LIMIT 0, 20
> so far this works great, but, i need to take the result set, which
> will be a bunch of member_id's and do this query.
> SELECT COUNT(member_id) FROM logins WHERE member_id = ???????;
> where the ??????? are the member_id's from the JOIN above. then, order
> by the total number of logins.

I would just use an IN clause with a separate query, unless you start
to see problems with performance. Pseudocode:

Array arrayResults = get_array_from_results(resultset);
String sql = "SELECT COUNT(member_id) FROM logins "
                    + "WHERE member_id IN (" + join(arrayResults, ',') + ")";

Get something like:
SELECT COUNT(member_id) FROM logins WHERE member_id IN (245,9378,23);

Matt Warden
Miami University
Oxford, OH, USA

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list