CODEWITHSUNDEEP
mysqlsqlselectgroup-bymax
Frank
Frank

Reputation: 2173

Select rows from a table based on max value in different table

I'm looking for a MySQL query to extract values like in the following example:

TABLE1:
 ID     name   
 25     ab       
 24     abc      
 23     abcd       
 22     abcde      
 21     abcdef 


 TABLE2:
 ID     ID_TABLE1   total       
 1      25          0
 2      25          1
 3      25          2
 4      25          3
 5      23          1
 6      22          0
 7      22          1
 8      21          0
 9      21          2
 10     24          10
 11     24          7

I want to return all TABLE1 rows where max value of total column (in TABLE2) is < 3. So the results should be:

 ID     name   
 23     abcd       
 22     abcde      
 21     abcdef

I tried this:

SELECT t1.*
FROM TABLE1 t1
INNER JOIN (
        SELECT MAX( total ) AS max_total, ID_TABLE1
        FROM TABLE2
        GROUP BY total, ID_TABLE1
) t2
ON t1.ID = t2.ID_TABLE1
WHERE t2.max_total < 3

but it's not the result I want.

Upvotes: 2

Views: 3019

Answers (6)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT t1.ID, t1.name
FROM TABLE1 t1
INNER JOIN (SELECT ID_TABLE1, MAX(total) AS max_total 
            FROM TABLE2 
            GROUP BY ID_TABLE1 
          ) t2 ON t1.ID = t2.ID_TABLE1 
WHERE t2.max_total < 3;

Upvotes: 3

mhawke
mhawke

Reputation: 87084

There's a simpler way without using GROUP or MAX:

SELECT * FROM table1
WHERE id NOT IN (
    SELECT id_table1 FROM table2 WHERE total >= 3
);

The subquery selects all rows in table2 that have a total >= 3. Then we select those rows from table1 that are not in the subquery result.

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Here is a way to do using left join without using any subquery and group by clauses.

select t1.* from table1 t1
join table2 t2
on t1.id = t2.id_table1
left join table2 t3 on
t2.id_table1 = t3.id_table1
and t2.total < t3.total
where t3.id is null
and t2.total < 3

Another way is 

select t1.* from table1 t1
join table2 t2 on t1.id = t2.id_table1
where not exists(
  select 1 from table2 t3
  where t2.id_table1 = t3.id_table1
  and t2.total < t3.total
)
and t2.total < 3;

Upvotes: 1

Dhaval
Dhaval

Reputation: 2379

 SELECT t1.*
FROM TABLE1 t1
INNER JOIN (
    SELECT   MAX( total ) AS max_total, ID_TABLE1
    FROM     TABLE2
    GROUP BY ID_TABLE1
    having t2.max_total < 3
  ) t2
 ON t1.ID = t2.ID_TABLE1

Upvotes: 1

M Prabhu
M Prabhu

Reputation: 58

SELECT t1.*

FROM TABLE1 t1

INNER JOIN (

    SELECT MAX( total ) AS max_total, ID_TABLE1
    FROM TABLE2
     where total > 3  GROUP BY total, ID_TABLE1

) t2 ON t1.ID != t2.ID_TABLE1

Upvotes: 0

Mureinik
Mureinik

Reputation: 311393

Your inner query groups the results by id_table and by total. Since the maximum of total per total is the value itself, it makes the inner query somewhat meaningless. Just remove the total from the group by clause and you should be OK:

SELECT t1.*
FROM TABLE1 t1
INNER JOIN (
        SELECT   MAX( total ) AS max_total, ID_TABLE1
        FROM     TABLE2
        GROUP BY ID_TABLE1
) t2
ON t1.ID = t2.ID_TABLE1
WHERE t2.max_total < 3

Upvotes: 2

Related Questions