[RESOLVED] Sum Query Help

[lintz]

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

[jmcilhinney]

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

[lintz]

Simply awesome jmcilhinney...thanks

[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

[jmcilhinney]

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.

[FunkyDexter]

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:-

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.