# Thread: Select minimum from one table joining another...

1. Registered User
Join Date
Jul 2010
Location
Norway
Posts
45

## Unanswered: Select minimum from one table joining another...

Hello!
Fairly easy problem, at least it should have been...

I have two tables

Table1
ID carID cost
1 1 25,-
2 2 30,-
3 1 40,-
4 1 26,-

Table2
ID carname carmodel
1 Ferrari 1990
2 Porsche 1991
3 Jaguar 1992

Here I have two simple tables, where the carID in table1 is the ID in the second table.
Now I want to return the minimum cost from table 1 for ALL cars at once.
Result wanted:
Ferrari 25,-
Porsche 30,-

Jaguar will not be in the result, since there is no price on it, it is so simple, yet... Jesus!

Tried:
Code:
SELECT  t1.price, t2.car FROM Price as t1
INNER JOIN Car as t2 ON t1.carID = t2.ID
WHERE
( t1.price = (SELECT min(t3.price) FROM Price as t3 ))
GROUP BY t1.price, t2.car;
Returns only the lowest cost of all cars:
Ferrari, 25
I am stuck!

Any tips and/or suggestions are more than welcome!

Edit: "Your" WYSIWYG editor for text bugs a bit if I put in colors? It uses :[COLOR="rgb(65, 105, 225)"], which fails... at least here, Flock browser. I put in "COLOR=RED" instead!

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by ManyTimes
Now I want to return the minimum cost from table 1 for ALL cars at once.
Code:
SELECT t2.carname
, MIN(t1.cost) AS mincost
FROM table2 AS t2
INNER
JOIN table1 AS t1
ON t1.carID = t2.id
GROUP
BY t2.carname

3. Registered User
Join Date
Jul 2010
Location
Norway
Posts
45

Thanks, I managed to do it myself though. My bad was I grouped by several columns, which resulted in too many prices for a car, too many hours in a row in front of the computer does magical things to your thinking!