I am beating my brain against this one
I have 3 SQL Server 2005 tables
userawards:
id, awardamount, userid, dateawarded, awardtypeid
user:
id, firstname, lastname
awardtypes:
id, title
So if the awards
table had the rows
1, 300.00, 3, 01-01-2011, 1
2, 125.00, 3, 01-05-2011, 1
3, 50.00, 2, 01-05-2011, 2
user table rows
1, john, smith
2, mark, smith
3, bob, smith
award types
1, cash
2, prize
and I want the output to look similar to this
bob smith, 425.00, cash
mark smith, 50, prize
etc etc.
A user can have multiple awards, the results need to display unique users, but with there total award amount. in addition there needs to be 2 joins, one, to grab the users first name/last that's in a user table and the award type title.
So my query is looking like this (i know it doesn't work)
SELECT id, userid, awardtypeid, SUM(awardamount)
FROM awards a
LEFT JOIN userinfo ui ON ui.userid = a,userid
LEFT JOIN awardtypes ON awardtypesid = a.awardtypeid
GROUP BY userid
Is this even possible?