mattesj mattesj - 4 months ago 9
SQL Question

Select from one table based on another tables values

I have a question if it's possible to select from one table based on another tables values in the same query. In my case I have two tables named

follow
and
score
.

Follow

------------------------
| follower | following |
------------------------
| Uname2 | Uname1 |
------------------------


Score

--------------------
| username | score |
--------------------
| Uname1 | 1 |
--------------------
| Uname1 | 2 |
--------------------


What i'm tryin to accomplice is to first get who the user is following and then get that users total score. I have tried with inner join etc but can't get it to work.

Query

"SELECT follow.following, score.SUM('score') FROM follow INNER JOIN score ON follow.following=score.username WHERE follow.follower='Uname2'";

Answer

To get one record use this:

SELECT
  f.following,
  SUM(s.score) as score
FROM
  Follow f, Score s
WHERE
  f.following = s.username
  AND f.follower = 'Uname2'

To get the sum for each try:

SELECT
  f.follower,
  f.following,
  SUM(s.score) as score
FROM
  Follow f, Score s
WHERE
  f.following = s.username
GROUP BY f.follower

Here's a Fiddle example.

Comments