[thelist] mysql join and subqueries
Matt Warden
mwarden at gmail.com
Fri Jun 3 22:10:07 CDT 2005
Codi,
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
http://mattwarden.com
This email proudly and graciously contributes to entropy.
More information about the thelist
mailing list