Help with SQL Query Syntax

[Guest]

Can someone help me out with SQL query syntax? I don’t know how to properly
construct a join when I’m using an aggregate function, in this case Min().

Here is the data:

TABLE vegetables
veg color
--- -----
Apple red
Apple green
Banana yellow

TABLE colors
color hex
----- ---
red FF0000
green 00FF00
yellow FFFF00

Here is the query that uses the aggregate function:

SELECT vegetables.veg, min(vegetables.color) as minColor FROM vegetables
GROUP BY vegetables.veg

Here is the result:

Veg minColor
--- ------
Apple green
Banana yellow

Great! Now give me a single SQL query, building upon the query above, that
uses joins and/or lookups to the colors table to give me this result:

Veg minColor hex
--- ------- ---
Apple green 00FF00
Banana yellow FFFF00

 

[giorgio rancati]

Hi, Russell

 

[Guest]

I think what you want is:

SELECT vegetables.veg, min(vegetables.color) as minColor
FROM vegetables INNER JOIN Colors
ON vegetables.color = colors.color
group by vegetables.veg;

Good Luck!

 

[giorgio rancati]

Hi, Chaim

your Query does not produce this result

Veg minColor hex
--- ------- ---
Apple green 00FF00
Banana yellow FFFF00



Bye
Giorgio

 

[Guest]

True enough. Small problem with optimistic reading ;-( I stand corrected.

--
Chaim

Hi, Chaim

your Query does not produce this result

Veg minColor hex
--- ------- ---
Apple green 00FF00
Banana yellow FFFF00



Bye
Giorgio

 

[giorgio rancati]

there are other manners
----
SELECT V.veg,V.minColor,C.Hex
FROM (SELECT veg, min(vegetables.color) as minColor
FROM vegetables
GROUP BY veg) AS V
INNER JOIN Colors AS C ON V.minColor=C.color
----
then Russell will monitor as is faster

Bye
Giorgio

True enough. Small problem with optimistic reading ;-( I stand corrected.