Trouble with getting MAX value of different Tables

[Diogo Martinho]

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?

[BitBlt]

To begin with, the test data you show for the "Group" table is wrong. I assume you meant that both CarID 2 and 3 were supposed to be in GroupID 2.

Next is, if you're expecting to get the max price for a group, you have to leave out Car.Name as part of your grouping criteria. Otherwise, every car will be listed, and as such, will have it's own price as the max. Maybe try something like this:

SELECT Group.GroupID, Max(Price.Price) as 'Price' 
From Price, Car, Group
Where Price.CarID = Car.CarID 
and Group.CarID = Car.CarID
Group by Group.GroupID

That should get you the maximum price for the entire group.

Hope that's what you were looking for! Good luck!

[code739]

please in your every table structure put a primary key

[blocblue]

Try using INNER JOIN in your query instead:

SELECT `group`.`GroupID`, `car`.`Name`, MAX(`price`.`Price`) AS `Price`
FROM `car`
INNER JOIN `group` ON (`group`.`CarID` = `car`.`CarID`)
INNER JOIN `price` ON (`price`.`CarID` = `car`.`CarID`)
GROUP BY `car`.`CarID`