[thelist] mysql join and subqueries

[partyarmy]

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

On 6/3/05, Phil Turmel <philip at turmel.org> wrote:
> 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
> --
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>