CODEWITHSUNDEEP
oracle-databaseoracle10g
user334560415
user334560415

Reputation: 17

checking values from two tables?

I had two tables table1 contains a list of customer and their info and there is a customer id for each one table2 contains a list of request from all with and customer id for each request as foreign key

and i want to check the customer who has request and who is not

plz how should that be done logical is it possible to eqaul between two queries ?

thanks

Upvotes: 0

Views: 25

Answers (1)

APC
APC

Reputation: 146239

You have two tables, the first with one row per customer, the other with zero to many rows per customer. So you need two techniques: one to reduce the second table to one row per customer and the other to join that result set to the first table. These techniques are an aggregating sub-query and an outer join respectively.

select c.customer_id
       , nvl(r.req_count, 0) as no_of_reqs
from   customer c
       left outer join ( select customer_id
                                , count(*)  as req_count
                         from   customer_req 
                         group by customer_id ) r
        on c.customer_id = r.customer_id

Upvotes: 1

Related Questions