[SOLVED] Finding the lowest number within a join?

[plautzer]

Hi,

 

I have following table set:

 

TABLE A

ID | attributes ..

1

2

TABLE B

TABLEA_ID | NAME_ID | Number

1 | 1 | 5

1 | 3 | 4

1 | 2 | 9

TABLE Names

ID | Name

4 | NameA

5 | NameB

9 | NameC

 

and I want to find the Name that has the lowest number of in TABLE B.

 

I tried following query:

 

Select a.id, min(b.number), c.name From
tableA a left join tableB b on (a.id = b.tableA_id)
left join Names c on (c.id = b.Name_id)

 

When Im running the query I get the right number but the c.name isnt the right one.

Do u know why that is?

 

I thought of somehow filtering the min(b.number) within the join. But it wont work.

 

tableA a left join tableB b on (a.id = b.tableA_id and b.number = min(b.number))

 

Greetz,

Plautzer

 

[kickstart]

Hi

 

Think you would need something like this:-

 

SELECT a.id, b.Number, c.name
FROM Tablea a
JOIN (SELECT tablea_id, min(number) AS Number from  TableB GROUP BY tablea_id) b
ON a.id = b.tablea_id
JOIN TableB c
ON b.tablea_id = c.tablea_id
AND b.Number = c.number
JOIN Names d
ON c.NameId = d.ID

 

One problem with this SQL is that if there are 2 entries on TableB for the same TableA_Id and the same Number then it will give duplicates.

 

All the best

 

Keith

[plautzer]

works fine, thx!