SQLite conditional SELECT statement - multiple criteria but single statement
<p>I have 2 tables, one is a list of customers, the other a list of historic and future delivery dates for these customers.</p>

<p><strong>Convenience code to create example table:</strong></p>

<pre><code>CREATE TABLE Customers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,

  name TEXT
);

INSERT INTO Customers (name) VALUES ('David');
INSERT INTO Customers (name) VALUES ('John');
INSERT INTO Customers (name) VALUES ('Anne');

CREATE TABLE Orders (
  id INTEGER,
  item TEXT,
  delivery_date TEXT
);

INSERT INTO Orders VALUES  ( 1, 'gopro' , '2016-04-05');
INSERT INTO Orders VALUES  ( 1,'car', '2015-12-30');
INSERT INTO Orders VALUES  ( 1,'watch', '2015-10-20');
INSERT INTO Orders VALUES  ( 2, 'laptop', '2016-04-15');
INSERT INTO Orders VALUES  ( 3, 'car', '2016-01-15');
INSERT INTO Orders VALUES  ( 3,'cup', '2015-11-30');
</code></pre>

<p>I need to create a SQL statement that shows all customers and <strong>their</strong> most recent and next delivery date.</p>

<p>(Something like - SELECT * from Customers with recent and next item and date FROM Orders.)</p>

<p><strong>Expected Result Table</strong></p>

<pre><code>Name, Most Recent Item, Recent Date, Next Item, Next Date
David, car, 2015-12-30, gopro, 2016-04-05
John, NULL, NULL, laptop, 2016-04-15
Anne, car, 2016-01-15, NULL, NULL
</code></pre>

<p>But the closest I can get with a single statement is:</p>

<pre><code>SELECT
(SELECT o.item from Orders WHERE delivery_date &lt; date('now') ORDER BY delivery_date DESC LIMIT 1) as Last_Item_Ordered,
(SELECT o.delivery_date from Orders WHERE delivery_date &lt; date('now') ORDER BY delivery_date DESC LIMIT 1) as Last_Delivery_Date,
(SELECT o.item from Orders WHERE delivery_date &gt;= date('now') ORDER BY delivery_date ASC LIMIT 1) as Next_Item_Ordered,
(SELECT o.delivery_date from Orders WHERE delivery_date &gt;= date('now') ORDER BY delivery_date ASC LIMIT 1) as Next_Delivery_Date,
c.*
FROM (Orders o INNER JOIN Customers c ON c.id = o.id)
GROUP BY c.name
ORDER BY c.id; 
</code></pre>

<p>and the <strong>Actual results</strong> are as this:</p>

<pre><code>Last_Item_Ordered   Last_Delivery_Date  Next_Item_Ordered   Next_Delivery_Date  id  name
watch        2015-10-20      watch       2015-10-20      1       David
laptop       2016-04-15      laptop      2016-04-15      2       John
cup          2015-11-30      cup         2015-11-30      3       Anne
</code></pre>

<p>Todays date is 21st February 2016, hence some values are expected to be NULL for the above customers future results.</p>

<p>If anyone can help, be greatly appreciated. thanks</p>