CODEWITHSUNDEEP
phpmysqlsqlmysqli
mattesj
mattesj

Reputation: 609

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'";

Upvotes: 0

Views: 96

Answers (3)

ShawnOrr
ShawnOrr

Reputation: 1329

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.

Upvotes: 1

Suraj
Suraj

Reputation: 3137

Use GROUP BY like this. Hope it will help. 

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

Upvotes: 0

Owais Ali
Owais Ali

Reputation: 724

You probably need to include a GROUP BY clause here, at the end of your query add GROUP BY follow.following.

Upvotes: 0

Related Questions