Select minimum from one table joining another...

ManyTimes · #1 (**permalink**) 02-20-11, 16:21

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!

r937 · #2 (**permalink**) 02-20-11, 16:50

Quote:

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

ManyTimes · #3 (**permalink**) 02-20-11, 17:22

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech