0

I have two tables inside my MySQL database and i add and retrieve information via PHP.

The first, contains entries like this:

id   username

21   user123
22   user65

The second, contains entries like this:

level   id    value

1       21    188
2       21    333
3       21    567
1       22    78

The id in table 1 is the same id used in table 2. I'm trying to retrieve the 'value' for the highest 'level' in table 2. Currently, im able to get the highest level for any given id using:

query("SELECT MAX( level ) AS max FROM `table2` WHERE `id` LIKE '".$table1ID."'");

Where i'm stuck is how to then get the 'value' based on that particular 'level' and 'id', is a join needed?

EDIT: What i need is an SQL statement that would return '567' as that is the 'value' associated with the highest 'level' for the 'id' 21. Basically, for table 2, given an 'id' (21 for example), the sql statement would find the highest 'level' for that 'id', and return the 'value', which would be 567.

Oblivion
  • 585
  • 2
  • 8
  • 26

2 Answers2

2

Simply use a subquery:

select t2.*
from table2 t2
where t2.level = (select max(tt2.level) from table2 tt2 where tt2.id = t2.id);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • @Oblivion . . . This returns the rows (which has the value) which are the highest values for each id. Have you tried running this? – Gordon Linoff Dec 01 '18 at 12:23
-1

Try 

<?php
$query = "SELECT t1.username,t2.MAX(level), t2.value from table2 t2 join table1 t1 on t2.id = t1.id WHERE tzblr2.level = 3 GROUP BY t1.username"
?>

to retrieve table2.value of max level for all users.

This one is for maximu lvl and value only for a specific user (max)

<?php
$query = "SELECT t2.MAX(level), t2.value from table2 t2 join table1 t1 on t2.id = t1.id WHERE tzblr2.level = 3 WHERE t1.id = ".$id.";";
?>
Dice
  • 236
  • 1
  • 8
  • Hi, I edited my question. I need it to grab the value for the highest level, which isnt always 3, could be anything. – Oblivion Dec 01 '18 at 03:17
  • I'm not trying to get user, im trying to get value of the highest level for a particular id. – Oblivion Dec 01 '18 at 03:29