CODEWITHSUNDEEP
mysqljoinleft-join
Jim Miller
Jim Miller

Reputation: 3329

Doing a leftJoin to get missing values, but getting something other than zero for the values?

A slight variant on the old leftJoin question: I have two tables that I want to join:

ID Val1
1  100
2  200
3  300

and

ID Val2
2 50

Following combine two tables and fill in missing values in mysql and others, this:

SELECT Table1.ID, Table1.Val1, COALESCE(Table2.Val2, 0) AS Val2
FROM Table1 LEFT OUTER JOIN Table2 USING (ID)

will give me the usual result:

ID Val1 Val2
1  100  0
2  200  50
3  300  0

My question: Is there a way to get something other than zero as the missing values, perhaps:

ID Val1 Val2
1  100  9999
2  200  50
3  300  9999

I'd like to get the results ordered so that the items with missing values come at the end of the set; something like this would seem to let me do it (with some additional ORDER BY stuff, of course).

Any thoughts? If this can't be done, is there another way I can get the ordering the way I'd like? Thanks!

Upvotes: 0

Views: 47

Answers (1)

Haleemur Ali
Haleemur Ali

Reputation: 28313

Use COALESCE(Table2.Val2, 9999) instead of COALESCE(Table2.Val2, 0).

That's what COALESCE does, if the first expression is NULL then it sets the next expression.

If your main goal is to place the 0-valued rows at the bottom, and order the rest in ascending order, you can do the following.

SELECT 
  Table1.ID, 
  Table1.Val1, 
  COALESCE(Table2.Val2, 0) AS Val2
FROM Table1 
  LEFT OUTER JOIN Table2 USING (ID)
ORDER BY 
  (CASE WHEN Table2.Val2 IS NULL then 1 ELSE 0 END), 
  Table2.Val2

This would produce a result set ordered like this:

ID Val1 Val2
2  200  50
1  100  0
3  300  0

Upvotes: 2

Related Questions