StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL - Find account for which all the orders have completed
text
Body
copied!<p>I have been roaming in google with this for couple of hours now.</p> <p>Two tables are there:</p> <pre><code>AccountTable ID | AccountID | OrderID OrderTable ID | OrderID | OrderStatus </code></pre> <p>OrderStatus can have values 1,2,3,4 with 4 being completed status.</p> <p>In order table, multiple rows are added as the order status is being updated. So for a completed order, you have will have 4 rows with 1, 2, 3 and 4 status respectively, in OrderTable.</p> <p>Single account can have multiple orders.</p> <p>I want to find the accounts for which all the orders have completed.</p> <p>I found up to this much:</p> <pre><code>select * from AccountTable INNER JOIN OrderTable ON AccountTable.OrderID = OrderTable.OrderID AND OrderTable.OrderStatus = 4 </code></pre> <p>This will pick accounts for which there is atleast one order in completed status.</p> <p>But this won't cover the scenario when one order is completed and the other order for the same account is not. I don't want to pick the account then.</p> <p>EDIT: I just wanted to show some data to make this clearer:</p> <pre><code>AccountTable 1 | Name1 | Order1 2 | Name1 | Order2 3 | Name2 | Order3 4 | Name2 | Order4 </code></pre> <hr> <pre><code>OrderTable 1 | Order1 | 1 2 | Order1 | 2 3 | Order1 | 3 4 | Order2 | 1 5 | Order2 | 2 6 | Order2 | 3 7 | Order2 | 4 8 | Order3 | 1 9 | Order3 | 2 10 | Order3 | 3 11 | Order3 | 4 12 | Order4 | 1 13 | Order4 | 2 14 | Order4 | 3 15 | Order4 | 4 </code></pre> <p>I want to retrieve only Name2 and NOT Name1.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload