SP from 3 tables

Last Post 26 Oct 2007 08:42 AM by tom27. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
roma
New Member
New Member

--
26 Oct 2007 06:08 AM
Hi,

I need a help to create SP from 3 tables.

I have Customer(custID, CustNmae), CustXRef(custID, AcctID) and Account (AcctID, AcctNumber, Balance, OpenAcct).

I tried to find CustName which has 3 AcctNumber , at list 1 OpenAcct =Y’, and Balance greater than average balance.

My SP doesn’t work for me and looks ugly.
Thanks
tom27
New Member
New Member

--
26 Oct 2007 08:42 AM
Assuming that you need a customer that has 3 accounts and in that atleast one account is open and has Balance > AVG balance here is the query


SELECT DISTINCT custName
FROM ( SELECT custID
FROM CustXRef
GROUP BY custID
HAVING COUNT(custID) = 3) AS Derv
INNER JOIN CustXRef cr on cr.custID = Derv.custID
AND EXISTS(SELECT *
FROM account ac
WHERE ac.AcctID = cr.AcctID
AND AC.Balance > (SELECT AVG(Balance) FROM account)
AND AC.OpenAcct ='Y' )
INNER JOIN Customer C ON Derv.custID = C.custID
roma
New Member
New Member

--
26 Oct 2007 09:47 AM
Thank you tom27
You are not authorized to post a reply.

Acceptable Use Policy