1

I have two tables.

Table 1:

A   B 
------
1   'A'
2   'B'
3   'C'

Table 2:

   A   C
----------
   2   15
   3   20

I'm using the following query:

SELECT table1.A, table1.B, table2.C
FROM table1
LEFT JOIN table2
ON table1.A = table2.A

The problem arises in the output which is:

    A   B   C
---------------
    1   'A'  null
    2   'B'  15
    3   'C'  20

Is there a way to get the null as 0, as I have to do an order by on table2.C. Any help is appreciated.

My expected output is:

    A   B   C
---------------
    3  'C'  20
    2  'B'  15
    1  'A'  0 

ps: I tried with IFNULL(table2.C, 0). Didn't work.

closed as off-topic by Philᵀᴹ, dezso, mustaccio, Vérace, John aka hot2use Apr 24 at 5:32

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Philᵀᴹ, dezso, mustaccio, Vérace, John aka hot2use
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    I don't know which DBMS you use, but there is COALESCE(table2.C, 0) on PostgreSQL – TurboGraphxBeige Apr 23 at 16:07
  • Oh that worked. Thank you so much!! But the row shows up at the beginning. Do you have a suggestion, to put it at the bottom. Am i doing something wrong. – Ron Apr 23 at 16:12
  • 1
    Check out ORDER BY – TurboGraphxBeige Apr 23 at 16:16
  • I did order by table2.c but it works for the other columns not this one. – Ron Apr 23 at 16:25
  • Ok I got it, I renamed the column table2.C after the COALESCE and then did order by on that. That worked. Thank you so much for the help. – Ron Apr 23 at 16:37
0

This may be the most efficient solution:

SELECT A, t1.B, t2.C
FROM   table1 t1
LEFT   JOIN table2 t2 USING (A)
ORDER  BY t2.C NULLS FIRST;

NULL values are sorted last by default. You can reverse that with NULLS FIRST. See:

Not the answer you're looking for? Browse other questions tagged or ask your own question.