[RESOLVED] Sum Query Help

I've got 3 tables which I've outlined below. What I'm trying to display is the 'UserName' from table1 along with the sum of 'Money' from table 3 where 'FriendName' in table2 = 'FriendName' in table3.

So the end result would display. Any help would be appreciated.
Bill 150
Ben 50
Frank 0

Table1
ID UserName
1 Bill
2 Ben
3 Frank

Table2
UserID FriendName
1 Steve
1 John
2 Steve
3 Luke

Table3
FriendName Money
Steve 50
Mark 30
John 100

SQL Code:

SELECT Table1.UserName, SUM(Table3.Money)
FROM Table1
INNER JOIN Table2 ON Table1.ID = Table2.UserID
INNER JOIN Table3 ON Table2.FriendName = Table3.FriendName
GROUP BY Table1.UserName

Simply awesome jmcilhinney...thanks :thumb::thumb:

The above query displays the following result:
Bill 150
Ben 50

Following on from the above query I am now trying to get the UserName from table1 where 'ID' in table1 = 'UserID' in table2 and 'FriendName' in table2 doesn't exist in table3.

The end result would be:
Frank

Quote:

---

Originally Posted by lintz

The above query displays the following result:
Bill 150
Ben 50

Following on from the above query I am now trying to get the UserName from table1 where 'ID' in table1 = 'UserID' in table2 and 'FriendName' in table2 doesn't exist in table3.

The end result would be:
Frank

---

New topic = new thread.

Code:

---

Select Username
From Table1
Inner Join Table2
  On Table1.ID = Table2.ID
Where Not Exists
  (Select *
  From Table3
  Where Table2.FriendName = Table3.FriendName)

---

Mind you, if a user had 2 friends in table 2 and neither existed in table 3 you would see them twice. If you only want to see them once you probably want this:-

Code:

---

Select Username
From Table1
Where Not Exists
  (Select *
  From Table2
  Inner Join Table3
      On Table2.FriendName = Table3.FriendName
  Where Table2.UserID = Table1.UserID)

---

although that would also include users with no friends at all.