How to retrieve columns of nested tables in SQL-CodePudding

This might be a basic question , but how do you retrieve 2nd column from inner table when using NOT in function

For example , i have 2 tables Customer(Customer id , customername) Orders (Order id , Order date , customerid) , here customer id is a formatted foreign key

I am writing a query

select * 
from customer
where customerid not in (select case when '1' then substr(customerid,1,2)
                                     when '2' then substr(customerid , 1,3)
                                     else customerid 
                                     end  from Orders)

In this query , how do we retrieve Order id and Order date ?

Thanks in advance

CodePudding user response：

Have a try on this:

select customer.*, orders.orderid, orders.orderdate 
from customer 
full outer join 
(
  select case when substr(customerid,1,2) = '1' then substr(customerid,1,2)
              when substr(customerid,1,3) = '2' then substr(customerid,1,3)
              else customerid 
         end as customid
         , orderid, orderdate
  from Orders
) orders on (customer.customerid = orders.customid)
where customer.customerid is null
;

Example:

with customer as (
  select 'James' as name, '100' as customerid from dual 
  union all 
  select 'Mike' as name, '101' as customerid from dual 
  union all 
  select 'Steve' as name, '102' as customerid from dual 
  union all 
  select 'Nora' as name, '103' as customerid from dual 
),
orders as (
  select 1001 as orderid, TO_DATE('2020-06-01T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') as orderdate, '100' as customerid from dual 
  union all 
  select 1002 as orderid, TO_DATE('2020-07-21T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') as orderdate, '102' as customerid from dual 
  union all 
  select 1003 as orderid, TO_DATE('2020-07-22T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') as orderdate, '2' as customerid from dual 
  union all 
  select 1004 as orderid, TO_DATE('2020-06-05T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') as orderdate, '103' as customerid from dual 
  union all 
  select 1005 as orderid, TO_DATE('2020-06-05T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') as orderdate, '101' as customerid from dual 
)
select customer.*, orders.orderid, orders.orderdate 
from customer 
full outer join 
(
  select case when substr(customerid,1,2) = '1' then substr(customerid,1,2)
              when substr(customerid,1,3) = '2' then substr(customerid,1,3)
              else customerid 
         end as customid
         , orderid, orderdate
  from Orders
) orders on (customer.customerid = orders.customid)
where customer.customerid is null
;

Not exactly clear to me is your customer id formatted foreign key. Could you provide some examples, please?

CodePudding user response：

Try this:

select c.customerid, c.customername, o.id, o.date 
  from orders o, customer c 
 where not exists (select 1 
                     from orders o2 
                    where c.customerid = o2.customerid 
                      and o.id = o2.id);