I'm having some trouble with selecting the Max values of three different tables in my SQL.
I have 3 tables
Car [CarID, Name] - Price [CarID, Price] - Group [GroupID, CarID]
With these Values
Car:
CarID --- Name
1 ------- Car1
2 ------- Car2
3 ------- Car3
Price:
CarID --- Price
1 ------- 500
2 ------- 1000
3 ------- 600
Group:
GroupID --- CarID
1 --------- 1
2 --------- 2
2 --------- 2
The output should be:
GroupID - Name - MaxValue
1 ------- Car1 ---- 500
2 ------- Car2 ---- 1000
I'm using this query:
SELECT Group.GroupID, Car.Name, Max(Price.Price) as 'Price' From Price, Car, Group
Where Price.CarID = Car.CarID and Group.CarID = Car.CarID
Group by Car.Name, Group.GroupID
And the Result is:
GroupID - Name
2 ------- Car2
2 ------- Car3
1 ------- Car1
What am I doing wrong?