0

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?

Edited by Diogo Martinho

4
Contributors
3
Replies
4
Views
4 Years
Discussion Span
Last Post by blocblue
0

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!

0

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`
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.