CODEWITHSUNDEEP
sqlpostgresql
lucky.expert
lucky.expert

Reputation: 783

SQL inner join not returning records with blank values

I have 2 different types of records in my 'items' table (postgreSQL database). Some items have invoiceid associated, which has customer information associated. The other items in my items table do not have an invoice number associated.

I am trying to return a list of items with invoice date and customer names. The items that don't have invoice or customerer associated will also show, but those fields will just be blank. The problem is with my current sql statment. It only shows the items with invoice info associated. 

select items.ItemID, items.qty, items.description, customers.firstname,
customers.lastname, invoices.InvoiceDate, items.status 
from items 
inner join Invoices on items.InvoiceID = Invoices.InvoiceID 
inner join customers on Invoices.CustomerID = Customers.CustomerID 
where Items.Status = 'ONTIME' 
ORDER BY InvoiceDate asc

Any ideas how I can get all records to show, or is it even possible? The fields that don't have data are NULL, i'm not sure if that is part of the problem or not.

Upvotes: 2

Views: 3736

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You want to use left outer join instead of inner join:

select i.ItemID, i.qty, i.description, c.firstname,
       c.lastname, inv.InvoiceDate, i.status 
from items i left outer join
     Invoices inv
     on i.InvoiceID = inv.InvoiceID left outer join
     customers c
     on inv.CustomerID = c.CustomerID 
where i.Status = 'ONTIME' 
order by InvoiceDate asc;

I also introduced table aliases to make the query a bit easier to read.

Upvotes: 6

Related Questions