partyarmy 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.
>
> not sure if this can be done without using subqueries for my server is
> still reluctant to upgrade.
>
> any help will be greatly appreciated. thanks guys.
>
> codi lechasseur
Hi codi,
Simplification opportunity first:
Your first query might not need a JOIN at all... Are all `member_id`s in
profile also in members (or supposed to be)? If so, your first query
simplifies to:
SELECT member_id
FROM profile
WHERE city = 'vancouver' AND category_id = '0' AND
profile_data LIKE '%three%'
LIMIT 0, 20
If `member_id`s could be in profile but not members, and you really want
to exclude them, at least change the LEFT JOIN to INNER JOIN. With
conditions on city, category, and profile_data from profiles, any
records from members that have no matching profile data will get tossed
anyways. Might as well do it in the JOIN, which should be much faster.
Now for the count you're looking for:
If you have the luxury of subquery support, I recommend this:
SELECT member_id, count(*) AS logincount
FROM logins
WHERE member_id IN (SELECT member_id
FROM profile
WHERE city = 'vancouver' AND category_id = '0' AND
profile_data LIKE '%three%'
LIMIT 0, 20)
GROUP BY member_id
ORDER BY logincount DESC
I'm presuming you want to know who's logging in the most, so I put the
DESC qualifier on the ORDER clause.
I'm not sure the LIMIT clause is really where you want it, though. You
may want to allow all matching member profiles, count 'em up, sort
descending, then cut it off at twenty results. Like so:
SELECT member_id, count(*) AS logincount
FROM logins
WHERE member_id IN (SELECT member_id
FROM profile
WHERE city = 'vancouver' AND category_id = '0' AND
profile_data LIKE '%three%')
GROUP BY member_id
ORDER BY logincount DESC
LIMIT 0, 20
But you might not be able to use subqueries, so convert to a JOIN instead:
SELECT logins.member_id, count(*) AS logincount
FROM logins INNER JOIN profile
ON logins.member_id = profile.member_id
WHERE city = 'vancouver' AND category_id = '0' AND
profile_data LIKE '%three%'
GROUP BY logins.member_id
ORDER BY logincount DESC
LIMIT 0, 20
HTH,
Phil