Select data from another table if exists, if not display null

Question

I have two tables.

Invoices

ID | Amount
-----------
1  | 123.54
2  | 553.46
3  | 431.34
4  | 321.31
5  | 983.12

Credit Memos

ID | invoice_ID | Amount
------------------------
1  | 3          | 25.50
2  | 95         | 65.69
3  | 51         | 42.50

I want to get a result set like this out of those two tables

ID | Amount | Cr_memo
---------------------
1  | 123.54 |
2  | 553.46 |
3  | 431.34 | 25.50
4  | 321.31 |
5  | 983.12 |

I've been messing with joins and whatnot all morning with no real luck.

Here is the last query I tried, which pulled everything from the Credit Memo table...

SELECT A.ID, A.Amount FROM Invoices AS A
LEFT JOIN Credit_Memos AS B ON A.ID = B.invoice_ID

Any help or pointers are appreciated.

Your query works (if you add the column from the second table): sqlfiddle.com/#!6/b2a53/2/0 — Tim Schmelter
– Tim Schmelter, Commented Nov 22, 2014 at 17:07

Raging Bull · Accepted Answer · 2014-11-22 17:13:15Z

2

Your query would work fine. Just add Credit_memo.Amount with an alias:

SELECT Inv.ID,Inv.Amount,IFNULL(C.Amount,'') AS Cr_memo
FROM Invoices Inv LEFT JOIN
    Credit_Memos C ON Inv.ID=C.invoice_ID

Result:

ID  AMOUNT  CR_MEMO
1   124 
2   553 
3   431     25.50
4   321 
5   983

See result in SQL FIDDLE.

edited Nov 22, 2014 at 17:13

answered Nov 22, 2014 at 17:06

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3084846 Over a year ago

Ha, thank you. That worked perfect. I totally forgot about IFNULL.

Pரதீப் · Accepted Answer · 2014-11-22 17:17:50Z

0

You almost got the answer Left Outer Join is what you need but you missed to select Cr_memo from Credit_Memos table. Since you don't want to show Null values when there is no Invoices_ID in Credit Memos table use IFNULL to make NULL's as Empty string

SELECT A.ID, A.Amount, IFNULL(B.Cr_memo,'') AS Cr_memo
FROM Invoices AS A
LEFT JOIN Credit_Memos AS B 
ON A.ID = B.invoice_ID

edited Nov 22, 2014 at 17:17

answered Nov 22, 2014 at 17:07

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Comments

Rajib Ghosh · Accepted Answer · 2014-11-22 17:42:55Z

0

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

  SELECT A.ID, A.Amount, IFNULL(B.amount,0) AS Cr_memo  FROM Invoices AS A
  LEFT JOIN Credit_Memos AS B ON A.ID = B.invoice_ID

here is some useful link about left join link and another

answered Nov 22, 2014 at 17:42

Rajib Ghosh

6403 silver badges12 bronze badges

Collectives™ on Stack Overflow

Select data from another table if exists, if not display null

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related