Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2010

    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:
    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...

    SELECT DISTINCT t1.carname, MIN(t2.cost),
    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,

    I know I can do something like this...
    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. #2
    Join Date
    Apr 2002
    Toronto, Canada
    SELECT cc.carID
         , cc.carownerID AS ownerID
         , c.carname
         , AS owner
         , cc.cost
      FROM ( SELECT carID
                  , MIN(cost) AS lowestcost
               FROM carcost
                 BY carID ) AS m
      JOIN carcost AS cc
        ON cc.carID = m.carID
       AND cc.cost = m.lowestcost
      JOIN car AS c
        ON c.ID = cc.carID
      JOIN carowner AS co
        ON co.ID = cc.carownerID | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2010
    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!

    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:
    DECLARE @tblOwnerCost TABLE
    ( carID int, ownerID int, lowestcost float, carname nvarchar(255), name nvarchar(255) )
    ( carID int, ownerID int, lowestcost float )
    INSERT INTO @tblOwnerCost(carID, ownerID, lowestcost, carname, name)
    (SELECT t1.carID, t1.carownerID, t1.cost, t2.carname,
    			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(, '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!

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

  4. #4
    Join Date
    Nov 2004
    Provided Answers: 4
    How would I change r937's query into selecting also cars with a null value, I want all cars in the result
    To get all cars: in the FROM part, start with the Car table, then use left outer joins from there on with the other tables. I haven't looked at this thread in detail, but that is the general rule.
    With kind regards . . . . . SQL Server 2000/2005/2012

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts