Hello!
I've attached a script to the tables I am "using", and a picture of the tables.
I want this as the result:
Code:
carID, cost, name
1 22 Berg
The query I am currently having, which works, but not in the exact way I want it to...
Code:
SELECT t1.cost, t1.carID, t3.name
FROM
(
SELECT DISTINCT t1.cost, t1.carID, t2.carname
FROM
(
SELECT MIN(cost) as cost, carID FROM carcost
WHERE carcost.carID = 1
GROUP BY carID
)as t1
INNER JOIN car as t2 ON t1.carID = t2.ID
)as t1
INNER JOIN carcost as t2 ON t1.carID = t2.carID AND t1.cost = t2.cost
INNER JOIN carowner as t3 ON t2.carownerID = t3.ID
But the trouble comes when two owners sells his car for the exact same price. Then this results in "duplicates". Lets say Jack and Casper also deliveres car 1, at a cost of 22. Then this query results in:
Code:
carID, cost, name
1 22 Berg
1 22, Jack
1 22 Casper
I do not want "duplicates" like this, which ones of Berg, Jack or Casper should I pick to be returned? It does not matter, but, if you really need a criteria; the one with the lowest ID, the id in the "carowner"-table.
Any suggestions?
The "DISTINCT" in the "inner Select-statement", it returns only one lowest cost per car.
Then when I try to join a seller to the cost and car, it results in multiple results per car... And of course I've tried different joins (outer, inner, right, left...)
Edit: Ehm, quite lol...
Code:
DECLARE @tbl1 TABLE(cost int, carid int)
DECLARE @tbl2 TABLE(carid int, name nvarchar(255))
INSERT INTO @tbl1(cost, carid)
(
SELECT DISTINCT t1.cost, t1.carID
FROM
(
SELECT MIN(cost) as cost, carID FROM carcost
WHERE carcost.carID = 1
GROUP BY carID
)as t1
)
INSERT INTO @tbl2(carid, name)
(
SELECT t1.carID, t1.name
FROM
(
SELECT TOP(1) t1.carid, t3.name FROM @tbl1 as t1
INNER JOIN carcost as t2 ON t1.carid = t2.carID AND t1.cost = t2.cost
INNER JOIN carowner as t3 ON t2.carownerID = t3.ID
) as t1
)
SELECT t1.carid, t1.cost, t2.name FROM @tbl1 as t1
LEFT JOIN @tbl2 as t2 ON t1.carid = t2.carid
Indeed, this returns what I want, but I feel I am abusing temp tables...
Solved; but if you still have suggestions, without declaring temp tables...
dBforums
>
Selecting a distinct car with the lowest cost and displaying just one owner.
03-31-11, 15:31
Linear Mode
