[thelist] mysql join and subqueries

[Phil Turmel]

partyarmy wrote:
> thanks matt and phil
> 
> tried this one below, but get a unknown table name 'members' error.
> 
> i had to make some changes, and put the table names in front of a few
> column names. theres three different tables all together, logins,
> members, and profile.
> 
> SELECT logins.member_id, count(*) AS logincount
>   FROM logins INNER JOIN profile
>     ON logins.member_id = profile.member_id
>   WHERE members.city = 'vancouver' AND profile.category_id = '0' AND
>     profile.profile_data LIKE '%three%'
>   GROUP BY logins.member_id
>   ORDER BY logincount DESC
>   LIMIT 0, 20
> 
Cody,

Matt is correct, the simplification I tried to suggest doesn't apply, as 
`city` comes from `members`.

Try this:

SELECT logins.member_id, count(*) AS logincount
   FROM logins INNER JOIN profile
     ON logins.member_id = profile.member_id
     INNER JOIN members
     ON logins.member_id = members.member_id
   WHERE members.city = 'vancouver' AND profile.category_id = '0' AND
     profile.profile_data LIKE '%three%'
   GROUP BY logins.member_id
   ORDER BY logincount DESC
   LIMIT 0, 20


HTH,

Phil