Results 1 to 4 of 4
  1. #1
    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 15:22.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    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 01:49.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    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
    Wim

    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
  •