Trying to understand how SELECT MIN works 1

[3dColor]

SELECT tn, carId, photoId
FROM Car INNER JOIN CarP ON Car.carId = CarP.carFk
LEFT JOIN (
	SELECT MIN(photoId) AS photoId, carFk, tn 
		FROM Photos
		GROUP BY carFk) AS Photos
		ON Photos.carFk = Car.carId

I am getting a different result than what I think I should be getting.

When I output both "tn" and "photoId", the photoId results correctly with the lowest value for that column (for carFk), however the "tn" does not result from the same row as the photoId, it results from a different row.

It appears that the "tn" results in the lowest value and it not linked to the photoId row.

I thought SELELT MIN() returned the row for the lowest value for the column within the ().

 

[r937]

I thought SELELT MIN() returned the row for the lowest value for the column within the ()

not exactly -- MIN() returns the lowest value of all the values in that column, for the specified group


your problem is that in the subquery, you are grouping incorrectly (only mysql allows you to do this, all other databases will give you a syntax error)

you are grouping on carFk, and you are indeed getting the MIN(photoId) for each carFK, but you also have tn in the SELECT, and you are getting an unpredictable value for tn -- it will be one of the values in the carFK group, but not necessarily the value from the same row that the min(photoId) comes from

as you said yourself,

the "tn" does not result from the same row as the photoId, it results from a different row

this is exactly what the mysql docs warn you against when you group incorrectly


see 12.10.3 GROUP BY with Hidden Fields

as for how to fix it, that's another question, and i'm sorry, i can't figure out what "tn" is or how it relates to the lowest photoID



r937.com | rudy.ca

 

[TonyGroves]

To get the "tn" value which matches the MIN(photoid) for each carFK, you need to
do a self-join on the table:
[tt]
SELECT tn,carid,photoid
FROM
car
INNER JOIN carp ON car.carid=carp.carfk
LEFT JOIN
(
SELECT p.photoid,p.carfk,p.tn
FROM
(
SELECT MIN(photoid) photoid,carfk
FROM photos
GROUP BY carfk
) q1
JOIN photos p
ON q1.carfk=p.carfk AND q1.photoid=p.photoid
) q2
ON car.carid=q2.carfk
[/tt]

 

[3dColor]

Thanks Rudy,

I will have to think about what you have said and see if I can fix it with the info you provided.

While i am think about it, you asked about what "tn" was, it stands for ThumbNail (photo name).

Here is what the three tables look like that i am joining:

Car Table
---------
CarId | carModel
30 | Acura

CarP Table
---------
carFk | beginDate
30 | 1/15/2006

Photos Table
---------
photoId | carFk | tn
139 | 30 | front-of-car.jpg
140 | 30 | back-of-car.jpg
141 | 30 | side-of-car.jpg

 

[3dColor]

Thanks Tony. I think i see what you are doing and i will give it a try.

Thanks!

 

[3dColor]

Tony, Thanks that worked just great. You guys amaze me.