mySQL - Using data from another table.field to determine results in a query.

Question

Hello Hello,

Buried in mySQL syntax and query structure problems once more. I'm currently doing this query fine:-

SELECT DATE_FORMAT(`when`, '%e/%c/%Y')date
     , COUNT(`ip`) AddressCount
  FROM `metrics`
 WHERE `projID` = '$projID'
 GROUP BY DATE(`when`)

This works fine to find the specific number of ip address's hit. However, what I'm now trying to do is give an overall, for all a user's projects. But, this table doesn't contain a user's ID, the projects table does. They are linked by a 'projID'. So, what I'm asking is how I can link the two to get a result.

I.e. SELECT the bumph I want FROM metrics WHERE (ANOTHER TABLE's) userID = '1' or, WHERE any projID's = ones listed in the projects table. alongside the userID.

Now, a straight forward join doesn't seem to be my answer? I'm perplexed by joins in the first place. Any help grand appreciated.

Answer 1

You DO want a join here. I won't get this 100% right because I don't know your column names, but here's a stab at it.

It would be helpful if you posted more info about your two tables and their columns.

SELECT DATE_FORMAT(m.`when`, '%e/%c/%Y')date
     , COUNT(`ip`) AddressCount
     , o.`userID`
  FROM `metrics` m
  LEFT JOIN `other_table` o
    ON m.`userID` = o.`userID` -- you'll likely need to change this bit
 WHERE `projID` = '$projID'
   AND o.`userID` = [Value] -- optional
 GROUP BY DATE(`when`)

Answer 2

Just join them? As in:

SELECT DATE_FORMAT(`when`, '%e/%c/%Y')date
     , COUNT(`ip`) AddressCount
  FROM `metrics`, project_user_association_table puat
 WHERE `projID` = puat.projId
   AND puat.userId = 123456789
 GROUP BY DATE(`when`)

This using a table project_user_accociation, which has a field for user and project (so user to projects is many-to-many in my example)