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:

    1234 1
    1234 2
    1234 3
    1234 4
    3245 3
    3245 4
    5436 2
    5436 4

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

3245 1
3245 2
5436 1
5436 3

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).

Answer 1

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
        )

Answer 2

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..

Answer 3

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>