Fill in missing values in a SELECT statement

Question

I have a table with two columns, customer id and order. Let's say I have in total order IDs 1,2,3,4

All the customer can have all the four orders, like below:

You can see above that 3245 customer doesn't have order id 1 or 2. How could I print in the query output like:

EDIT: I don't have an order table, but I have a list of order's like we can hard code it in the query(1,2,3,4).

So what you are actually looking for are the GAPS (ie: those that do NOT have a given order number). Do you have a master list of order numbers that all customers SHOULD have? Doesn't sound right though. Because if you have one customer come in one time, but another comes in 100+ times, you don't want to expect everyone to have 100+ orders. — DRapp, Apr 29 '10 at 14:25
Are you tring to view the customers that are not linked to these orders? — Paul Williams, Apr 29 '10 at 14:26
here ..one customer at max can have only 4 orders and no one order is done twice by one customer. — Vijay, Apr 29 '10 at 14:27
i mainly need customers and orders which were not ordered by them among 1,2,3,4 — Vijay, Apr 29 '10 at 14:28

Quassnoi · Accepted Answer · 2010-04-29T14:54:10.043

SELECT  c.id, o.order
FROM    (
        SELECT  1 AS order
        UNION ALL
        SELECT  2 AS order
        UNION ALL
        SELECT  3 AS order
        UNION ALL
        SELECT  4 AS order
        ) o
CROSS JOIN
        (
        SELECT  DISTINCT id
        FROM    customer_orders
        ) c
WHERE   NOT EXISTS
        ( 
        SELECT  NULL
        FROM    customer_orders ci
        WHERE   ci.id = c.id
                AND ci.order = o.order
        )

If you have customers table, it becomes more simple:

SELECT  c.id, o.order
FROM    (
        SELECT  1 AS order
        UNION ALL
        SELECT  2 AS order
        UNION ALL
        SELECT  3 AS order
        UNION ALL
        SELECT  4 AS order
        ) o
CROSS JOIN
        customers c
WHERE   NOT EXISTS
        ( 
        SELECT  NULL
        FROM    customer_orders ci
        WHERE   ci.id = c.id
                AND ci.order = o.order
        )

Excellent.This is exactly what i need.BYW i am getting the expected output but could you plase explain the WHERE part of the query? — Vijay, May 01 '10 at 08:07
@benjamin: the query build all possible customer / order combinations with a `CROSS JOIN` and then filters them with `NOT EXISTS`, returning only those that have no match in `customer_orders`. — Quassnoi, May 01 '10 at 09:07

score 1 · Answer 2 · edited May 23 '17 at 12:07

Okay, there are two issues here. The first problem is turning a list of numbers into a rowset. There are a number of different ways of doing this, depending on how you get the numbers into the query. In the following example I use a function which turns a comma-separated string into a nested table, which can be treated as a regular table with the TABLE() function. This is not strictly relevant to the question you pose. If you're interested in this bit of the implementation see my post in this other thread.

The second part of the problem is identifying the missing Orders for each Customer. The obvious approaches - such as using NOT IN with a sub-query - won't work, because the Orders for Customer 1234 match all the Order IDs. We need to do is fill in the missing orders for each Customer. This can be done by using a LEFT OUTER JOIN combined with the PARTITION BY clause. It is then a simple matter to filter out the hits by embedding the LOJ query in an outer SELECT, like this:

SQL> select customer_id
  2         , missing_order_id
  3  from (
  4      select t42.customer_id
  5             , t42.order_id
  6             , nos.column_value as missing_order_id
  7      from  ( select * from table  (str_to_number_tokens('1,2,3,4'))) nos
  8      left outer join t42 partition by ( t42.customer_id )
  9      on nos.column_value = t42.order_id
 10      )
 11  where order_id is null
 12  /

CUSTOMER_ID MISSING_ORDER_ID
----------- ----------------
       3245                1
       3245                2
       5436                1
       5436                3

SQL>

DRapp · Answer 3 · 2010-04-29T14:46:05.457

aside from my comment, and your existing table, I would approach something like this...

select distinct
      a.Customer,
      b.OrderNumber
   from
      YourOrderTable a,
      ( select distinct OrderNumber from YourOrderTable ) b
   where 
      b.OrderNumber NOT IN
          ( select OrderNumber from
                YourOrderTable c
                where a.Customer = c.Customer
                  and b.OrderNumber = c.OrderNumber )

By doing a select distinct as the second table in the FROM clause and no specific join to it, you will get a Cartesian join... ie: for each customer, it will join to every possible order number.

Then, in your WHERE clause, the NOT IN SQL test will only allow the "b." order numbers where none exist in the SQL-subselect (c.)

This could be a very costly query, especially if you have many unique orders..

i have just 4 orders.so it would not be any costly – Vijay Apr 29 '10 at 14:52 — Vijay, Apr 29 '10 at 14:52

Fill in missing values in a SELECT statement

3 Answers3