Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45

    Unanswered: Select minimum from one table joining another...

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ManyTimes View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45


    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!

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
  •