# Thread: Select minimum with multiple columns from one table joining two others

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

## Unanswered: Select minimum with multiple columns from one table joining two others

Three tables which consist of cars, cost and owner.

Table1 (carcost)
ID carID carownerID cost
1 1 1 25,-
2 2 1 30,-
3 1 2 40,-
4 1 3 26,-

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

Table3 (carowner)
ID name
1 Berg
2 Casper
3 Jackie

Now I want a result looking like this:
Code:
```carid ownerid carname owner cost
1     1      Ferrari   Berg  25
2     1      Porsche   Berg   40```
Berg has the cheapest price of the ferrari, at 25,-
Only Berg owns a Porsche, so his cost and name is returned
None owns a jaguar, so nothing is returned for the jaguar

Basically printing out the lowest price for a car and the owner of the about to be sold car and the carname...

My tries, has been plenty...

Code:
```SELECT DISTINCT t1.carname, MIN(t2.cost), t3.name
FROM car as t1
INNER JOIN carcost as t2 on t1.ID = t2.carID
LEFT JOIN carowner as t3 on t2.carownerID = t3.ID
GROUP BY t1.carname, t3.name```

I know I can do something like this...
Code:
```SELECT DISTINCT t1.ID, MIN(t2.cost)
FROM car as t1
INNER JOIN carcost as t2 on t1.ID = t2.carID
GROUP BY t1.ID```
This results in distinct car names and the correct lowest cost. Then I could do a another query based on the result, to find who has the price and another query to find the car name based on the "t1.ID", but this is not what I want, I need it to be all in one query, returning everything in one query...

But... oh... suggestions?

Edit: Union all might help? No? hmm... damn
Last edited by ManyTimes; 03-08-11 at 14:22.

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Code:
```SELECT cc.carID
, cc.carownerID AS ownerID
, c.carname
, co.name AS owner
, cc.cost
FROM ( SELECT carID
, MIN(cost) AS lowestcost
FROM carcost
GROUP
BY carID ) AS m
INNER
JOIN carcost AS cc
ON cc.carID = m.carID
AND cc.cost = m.lowestcost
INNER
JOIN car AS c
ON c.ID = cc.carID
INNER
JOIN carowner AS co
ON co.ID = cc.carownerID```

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

Selecting all values first from a table that is returned from another select statement that returns carID and lowest cost for that car as the value/in a table M...
Then join the other tables onto M or each other..

Now it only remains to convert this into my real tables!

Brilliant..., thank you!

Edit:
I now need to return all cars even if they have no cost (as in no owner), but those cars should have a cost 0 and a owner named "none", any suggestions?

Well, here is my try:
Code:
```DECLARE @tblOwnerCost TABLE
( carID int, ownerID int, lowestcost float, carname nvarchar(255), name nvarchar(255) )
DECLARE @tbl TABLE
( carID int, ownerID int, lowestcost float )

INSERT INTO @tblOwnerCost(carID, ownerID, lowestcost, carname, name)
(SELECT t1.carID, t1.carownerID, t1.cost, t2.carname, t3.name
FROM
(
SELECT carID, MIN(cost) as lowestcost FROM carcost
INNER JOIN car ON carcost.carID = car.ID
WHERE car.ID = 1 OR car.ID = 2
GROUP BY carID
)as temp
INNER JOIN carcost as t1
ON temp.carID = t1.carID AND temp.lowestcost = t1.cost
INNER JOIN car as t2
ON temp.carID = t2.ID
INNER JOIN carowner as t3
ON t1.carownerID = t3.ID
);

INSERT INTO @tbl (carID, lowestcost, ownerID)
(
SELECT t1.ID, 0 as lowestcost, 0 as ownerID FROM car as t1
WHERE t1.ID = 1 OR t1.ID = 2
);

SELECT t1.carID, ISNULL(t2.ownerID, 0), ISNULL(t2.lowestcost, 0), ISNULL(t2.carname, 'Ingen'), ISNULL(t2.name, 'Ingen')
FROM @tbl as t1
LEFT JOIN @tblOwnerCost as t2 ON t1.carID = t2.carID```
It works, it gives me the result I want, so I should be happy? Well, no, I am not.

How would I change r937's query into selecting also cars with a null value, I want all cars in the result... A simple LEFT JOIN Car-Table within the "FROM (select carID, MIN(cost) as lowestcost)..." would do? I've tried! Failed!

Suggestions?
Last edited by ManyTimes; 03-09-11 at 00:49.

4. Registered User
Join Date
Nov 2004
Posts
1,428