Queries to complete! Help! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Queries to complete! Help!

Author

Topic

Jerry_Newlove
Starting Member

3 Posts

Posted - 2002-01-27 : 05:15:56

Hi,

My name is Jerry and I have a few problems with querreying. Below is the schema of my database.

Branch (Branch_name, Branch_id, Location, Telephone)
Customer (Customer_name, Customer_id, Address, Telephone, Branch_id)
Current Account (Current_account_no, Customer_id, Balance)
Deposit Account (Deposit_account_no, Customer_id, Balance)
Loan Account (Loan_account_no, Customer_id, Amount, Date_of_loan)

I need to create the following views of the database for different users requiremnets.

1) Customer_name, Current_account_no and Deposit_account_no
2) Customer_name, address, telephone_number (cust) and date of loan.
3) Account numbers of all three accounts with balances but no personal details.

I have created a query for (1) and it goes a little something like this:

SELECT Customer_name,Current_account_no,Deposit_account_no
FROM Customer LEFT OUTER JOIN Current_Account ON Customer.Customer_id=Current_Account.Customer_id
LEFT OUTER JOIN Deposit_Account ON Customer.Customer_id=Deposit_Account.Customer_id

The problem with this is that it will show every current account no, but if a customer has more deposit accounts than current, the same curent account numbers will be repeated for each different deposit account (where it should say null).

Querry (2) looks no trouble, but (3) is hard. Any advice you can give me is much appreciated. I also have to give a demonstration of a deadlock with this database, and then create a locking strategy to overcome the specific deadlock. I have an idea how lock it by not commiting trans so making it look as if multiple users have accessed it at the same time but still, your help could give me a better understanding of how it all works.

Thanks a lot,

Jerry.

Edited by - Jerry_Newlove on 01/27/2002 11:40:27

Edited by - Jerry_Newlove on 01/27/2002 12:03:03

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-27 : 06:50:08

Not too keen on the dtabase schema which is what is causing your problems.
If you don't want to change it then

(1)
SELECT Customer_name,Current_account_no,Deposit_account_no = null
FROM Customer JOIN Current_Account ON Customer.Customer_id=Current_Account.Customer_id
union
SELECT Customer_name,Current_account_no = null,Deposit_account_no
FROM Customer JOIN Current_Account ON Customer.Customer_id=Current_Account.Customer_id
LEFT OUTER JOIN Deposit_Account ON Customer.Customer_id=Deposit_Account.Customer_id

(3)
Similar to above but including balances

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-27 : 07:09:06

A few things, Jerry.
Firstly, I'm a bit suspicious of what Branch_id is doing in the Account tables: isn't Customer.Customer_id a foreign key in these tables? Unless Customer.Branch_id and *_Account.Branch_id are playing different roles, this doesn't look right.

Second, although it is certainly possible to do (1), I'm not sure it's what you want. Queries that try to 'paste' together columns that are independently (or arbitrarily) ordered tend to be slow, difficult to manage, and generally work against the concepts of SQL. You might consider whether the users are passing on the job of presenting the data.

For (3), are you just trying to concatenate the three account tables? That would be a union query like this:


SELECT 'C' AS Account_type, Current_account_no AS Account_no, Balance FROM Current_Account
UNION ALL SELECT 'D', Deposit_account_no, Balance FROM Deposit_Account
UNION ALL SELECT 'L', Loan_account_no, Amount FROM Loan_Account

Back to (1). To get this to work, you need to rank each customers current and deposit accounts so that there is something to join on that will not produce duplicates. Then you can use a full outer join to get the combined account data, and join that to the Customer table. As I said, it's ugly and likely to be slow:


SELECT Customer_name, Current_account_no, Deposit_account_no
FROM Customer
LEFT OUTER JOIN (
  SELECT COALESCE(c.Customer_id, d.Customer_id),
    Current_account_no, Desposit_account_no
  FROM (
    SELECT c1.Customer_id, c1.Current_account_no, COUNT(c2.Customer_id) rank
    FROM Current_Account c1
    LEFT OUTER JOIN Current_Account c2 ON c1.Customer_id = c2.Customer_id
      AND c1.Current_account_no < c2.Current_account_no
    GROUP BY c1.Customer_id, c1.Current_account_no) c
  FULL OUTER JOIN (
    SELECT d1.Customer_id, d1.Deposit_account_no, COUNT(d2.Customer_id) rank
    FROM Deposit_Account d1
    LEFT OUTER JOIN Deposit_Account d2 ON d1.Customer_id = d2.Customer_id
      AND d1.Deposit_account_no < d2.Deposit_account_no
    GROUP BY d1.Customer_id, d1.Deposit_account_no) d
  ON c.Customer_id = d.Customer_id AND c.rank = d.rank) ac
ON Customer.Customer_id = ac.Customer_id

Agh! Sniped! (Albeit with a different interpretation of the questions.)

Edit: Oops! Jerry spotted the deliberate error (careless cut-and-paste really!) -- try it now. In answer to the emailed question "why a full outer join?", it's to address the possibility that account holders could have more deposit accounts than current accounts or vice versa.

Edited by - Arnold Fribble on 01/28/2002 16:01:47

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources