By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,378 Members | 1,455 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,378 IT Pros & Developers. It's quick & easy.

Help with joining 2 tables

P: 3
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 :)
Mar 17 '08 #1
Share this Question
Share on Google+
5 Replies


debasisdas
Expert 5K+
P: 8,127
The join in this case is so simple

Kindly post your query that you are working on.
Mar 17 '08 #2

P: 3
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!
Mar 17 '08 #3

P: 3
anyone? i believe this is a simple problem but for the life of me i couldn't get it... :(
Mar 18 '08 #4

Delerna
Expert 100+
P: 1,134
How about this in a UDF
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION fnListCustomerBanks( @CustNo varchar(10) )
  2. RETURNS @tbl table (CustID varchar(10),
  3.                     BankID varchar(10),
  4.                     BankName varchar(10))
  5. AS
  6. BEGIN
  7.  
  8.     INSERT INTO @tbl
  9.     SELECT case When b.CustID=@CustNo then Cust_ID else null end  as Cust_ID,
  10.            Bank_ID,BankName 
  11.     FROM Bank_Table a
  12.     LEFT JOIN CustomerBank_Table b on a.Bank_ID=b.BankID
  13.  
  14.     RETURN
  15. END
  16.  

then you use the function like a parameterised query

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM dbo.fnListCustomerBanks('C015')
  3.  
Mar 18 '08 #5

Delerna
Expert 100+
P: 1,134
actually that will return the same bank multiple times
so this version of the UDF
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION fnListCustomerBanks( @CustNo varchar(10) )
  2. RETURNS @tbl table (CustID varchar(10),
  3.                     BankID varchar(10),
  4.                     BankName varchar(10))
  5. AS
  6. BEGIN
  7.  
  8.     INSERT INTO @tbl
  9.     SELECT max(case When b.CustID=@CustNo then Cust_ID else null end)  as Cust_ID,
  10.            Bank_ID,BankName 
  11.     FROM Bank_Table a
  12.     LEFT JOIN CustomerBank_Table b on a.Bank_ID=b.BankID
  13.     GROUP BY Bank_ID,BankName 
  14.     RETURN
  15. END
  16.  
should fix that.
Mar 18 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.