CODEWITHSUNDEEP
mysqljoin
JesseB1234
JesseB1234

Reputation: 147

MySQL query: select all ID's and corresponding max value from other table

What I want: selecting all ID's from table1 and a max value from table2. If table2 doesn't contain an id, 0 is its max value. Query I got so far:

SELECT t1.Id, max(t2.value) FROM table1 t1 JOIN table2 t2 USING(Id) GROUP BY t2.Id;

But the problem with this query is that it only returns an id if it is in both tables. How can I select all id's from table1 and a 0 or a max value from table2?

Upvotes: 0

Views: 274

Answers (1)

forpas
forpas

Reputation: 164064

This is the case to use a LEFT join:

SELECT t1.Id, coalesce(max(t2.value), 0) 
FROM table1 t1 LEFT JOIN table2 t2 
USING(Id) 
GROUP BY t1.Id;

I also changed the GROUP BY clause to use t1.id instead of t2.id.

Upvotes: 1

Related Questions