SQL query that has a lookup field

I have a table Invoices that has 3 fields:

InvoiceProductCode InvoiceQuantity InvoicePrice

InvoiceProductCode can be blank, but if it’s not blank, I would like it to be filled with a value from Products table.

• products table – ProductCode ProductName

Example 2 lines of data

InvoiceProductCode    InvoiceQuantity    InvoicePrice
3                     2                   5
                      1                   10  

SELECT InvoiceProductCode, InvoiceQuantity, InvoicePrice from Invoices 
INNER JOIN Products ON InvoiceProductCode = Products.ProductCode 
WHERE InvoiceNumber=3 

I would like both rows to be returned even though 2nd row’s InvoiceProductCode is empty (or zero), just that only the first row will have the ProductName; the 2nd row’s ProductName will be empty. i would like to do this in SQL without using Lookup table.

So final result should look like 2 rows:

InvoiceProductCode ProductName   Invoice Quantity    Invoice Price
3                  Laptop               2                   5
0                                       1                   10

instead of just 1 row:

InvoiceProductCode ProductName   Invoice Quantity    Invoice Price
3                  Laptop               2                   5

how do I structure a query? INNER JOIN on ProductCode will only give me 1 row – the one with the non-empty ProductCode, but the row without the ProductCode is left out.

Any advice?

thanks.

Answer

I think you want left join:

select coalesce(p.ProductName, ipc.InvoiceProductCode), . . . 
from InvoiceProductCode ipc left join
     Products p
     on ipc.InvoiceProductCode = p.ProductCode