sql query for 3 tables

Question

i have 3 tables (A,B,C) Table A -

ID Name
1  Sam
2  Manuel
3  Jane

Table B

ID Tab_A_ID Name
1   1       Meer
2   1       Kutti
3   2       Mikaro

Table C

ID Tab_B_ID Price
1  1        255.11
2  1        30.52
3  3        125.22

I need a query that shall pick up the top price for TableA-Name from TableC. So only 1 top price for 1 nae record. e.g.-

Sam - 255.11

Manuel - 125.22

How can i get this?

Answer 1

To get the max price per entry in A:

SELECT     a.Name,
           MAX(c.price)
FROM       a
INNER JOIN b
ON         a.id = b.tab_a_id
INNER JOIN c
ON         b.id = c.tab_b_id
GROUP BY   a.id, a.name

To get the max price per entry A per entry B:

SELECT     a.Name,
           b.Name
           MAX(c.price)
FROM       a
INNER JOIN b
ON         a.id = b.tab_a_id
INNER JOIN c
ON         b.id = c.tab_b_id
GROUP BY   a.id, b.id, a.name, b.name

Note that entries in A without corresponding entires in B or entries in B without corresponding entries in C will not appear in the result. Use LEFT JOIN if you want to include these in the result.