Help with joining 2 tables

[southpaw77]

Hi, I'm a beginner in sql so hopefully the masters in here can help me out with a simple problem...

I have 2 tables:

A) BANK_TABLE

BANK_ID BANK_NAME
B001 AAA
B002 BBB
B003 CCC
B004 DDD
...

B) CUSTOMERBANK_TABLE

CUST_ID BANK_ID
C001 B001
C001 B003
C005 B001
C005 B002
C010 B003
C015 B002
C015 B004
...

BANK_TABLE is the master bank table. CUSTOMERBANK_TABLE is linked to BANK_TABLE by BANK_ID (obviously) and stores all the customers' selected banks.

What I want to do is, given a CUST_ID (eg C015) I want to return all the banks from BANK_TABLE, and if a given bank is selected by C015, it will return the CUST_ID. This is the structure that I want the result:

CUST_ID BANK_ID BANK_NAME
<null> B001 AAA
C015 B002 BBB
<null> B003 CCC
C015 B004 DDD
<null> B005 EEE



So the query will return all the banks, and if a bank is selected by a customer who's not C015, it'll return a null/blank CUST_ID, and if the bank is selected by C015, it'll return this CUST_ID.

I hope this is clear enough, will appreciate any feedback :)

[debasisdas]

The join in this case is so simple

Kindly post your query that you are working on.

[southpaw77]

The join in this case is so simple

Kindly post your query that you are working on.

Exactly what I'm looking to hear :)

Here is my query:

select c.cust_id, b.bank_id, b.bank_name
from BANK b
left outer join CUSTBANK c on c.bank_id = b.bank_id


Im getting this result based on the examples above:

CUST_ID BANK_ID BANK_NAME
C001 B001 AAA
C005 B001 AAA
C005 B002 BBB
C015 B002 BBB
C001 B003 CCC
C010 B003 CCC
C015 B004 DDD

So assuming im sending a parameter custid=C015, i want the result to return all the banks (unique only), and if a bank is selected by C015, this cust_id will be returned as part of the result as explained in my first post.

Thanks!

[southpaw77]

anyone? i believe this is a simple problem but for the life of me i couldn't get it... :(

[Delerna]

How about this in a UDF

CREATE FUNCTION fnListCustomerBanks( @CustNo varchar(10) )
RETURNS @tbl table (CustID varchar(10),
                    BankID varchar(10),
                    BankName varchar(10))
AS
BEGIN
 
    INSERT INTO @tbl
    SELECT case When b.CustID=@CustNo then Cust_ID else null end  as Cust_ID,
           Bank_ID,BankName 
    FROM Bank_Table a
    LEFT JOIN CustomerBank_Table b on a.Bank_ID=b.BankID
 
    RETURN
END
 

then you use the function like a parameterised query

SELECT *
FROM dbo.fnListCustomerBanks('C015')
 

[Delerna]

actually that will return the same bank multiple times
so this version of the UDF

CREATE FUNCTION fnListCustomerBanks( @CustNo varchar(10) )
RETURNS @tbl table (CustID varchar(10),
                    BankID varchar(10),
                    BankName varchar(10))
AS
BEGIN
 
    INSERT INTO @tbl
    SELECT max(case When b.CustID=@CustNo then Cust_ID else null end)  as Cust_ID,
           Bank_ID,BankName 
    FROM Bank_Table a
    LEFT JOIN CustomerBank_Table b on a.Bank_ID=b.BankID
    GROUP BY Bank_ID,BankName 
    RETURN
END
 

should fix that.