Given table:
create table borrower (
customer_number char ( 1 0 ) ,
loan_number char ( 1 0 ));
question: Find the customer number (i.e., ID) of every customer that shares all of the loans that customer “1234” has. If the customer whose customer number is “1234” has loans “L1” and “L2”, you need to find all customers (including customer “1234”) that share both “L1” and “L2”.
I have created 4 entries in the table for the columns (customer_number, loan_number) are (1234, L1) (1234, L2) (1, LI)(1, L2)(2, L1). I am using query
select distinct customer_number
from borrower
where loan_number IN(
select loan_number
from borrower
where customer_number='1234'
but it is retrieving output such as
1234, 1, 2
I want only 1 and 1234(Customer_number) in my output because it is the only one having both loan number L1 and L2. Cutsomer_number "2" is not associated with both the loan_number, so it should come in the out put.
I have also tried "all" in place of "IN".
select distinct customer_number
from borrower
where loan_number= all(
select loan_number
from borrower
where customer_number='1234'
output: nothing
You could use a temporary table and a join.
/* create a table of the loans we are looking for */
create table Temp select distinct loan_number from Borrower
where customer_number = '1234';
/* join against the temp table and select borrowers with 3 matches */
select customer_number as 'Match'
from (select customer_number, COUNT(*) as C
from Borrower
inner join Temp on Borrower.loan_number = Temp.loan_number
group by customer_number) X
where C = 3
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments