Mutiple Joins too many rows - 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

Transact-SQL (2000)

Mutiple Joins too many rows

Author

Topic

gmcmenimen
Starting Member

3 Posts

Posted - 2003-01-12 : 20:10:29

I am trying to get data from three tables that will list only customers that have more than three accounts. I get the customers but it also lists all the accounts in the account table to each customer that I grouped out. I have tried everything that I can think of from temp tables to inner joins and I am probaly missing something. I am not very familiar with T-SQL procedures or function but can work my way through it. Here is my statement:

select c.cust_id as CustomerID, c.lname as Lastname,
c.fname as Firstname, c.mi as mi,
c.addr_1 as Address1, c.addr_2 as address2, c.city as City,
c.state as State, c.zip as Zipcode, c.ssn as SSN,
a.acct_id as AccountID, ad.acct_name as AccountType
from customer c, account a, account_descn ad
where (ad.acct_type = a.acct_type) and c.cust_id in
(select a.cust_id from account a
group by a.cust_id having COUNT(a.cust_id) > 2)

Here is what I get:

CustomerID Lastname Firstname mi Address1 address2 City State Zipcode SSN AccountID AccountType
200006 Story Ben S 23 Springhill Road Mobile AL 34125 451659825 101001 Savings
200006 Story Ben S 23 Springhill Road Mobile AL 34125 451659825 101002 Checking
200006 Story Ben S 23 Springhill Road Mobile AL 34125 451659825 101003 Auto Loan
200006 Story Ben S 23 Springhill Road Mobile AL 34125 451659825 101004 Credit Card
200006 Story Ben S 23 Springhill Road Mobile AL 34125 451659825 101005 Checking
200006 Story Ben S 23 Springhill Road Mobile AL 34125 451659825 101006 Mortgage
200006 Story Ben S 23 Springhill Road Mobile AL 34125 451659825 101007 Personal Loan
200008 Pritchett Martin R 6543 Scooter Road Falling Waters WV 25419 895653265 101001 Savings
200008 Pritchett Martin R 6543 Scooter Road Falling Waters WV 25419 895653265 101002 Checking
200008 Pritchett Martin R 6543 Scooter Road Falling Waters WV 25419 895653265 101003 Auto Loan
200008 Pritchett Martin R 6543 Scooter Road Falling Waters WV 25419 895653265 101004 Credit Card
200008 Pritchett Martin R 6543 Scooter Road Falling Waters WV 25419 895653265 101005 Checking
200008 Pritchett Martin R 6543 Scooter Road Falling Waters WV 25419 895653265 101006 Mortgage
200008 Pritchett Martin R 6543 Scooter Road Falling Waters WV 25419 895653265 101007 Personal Loan

gmcmenimen
Starting Member

3 Posts

Posted - 2003-01-12 : 20:15:10

I guess I didn't explain the topic very well. I get the entire account table and each account isd is given to everyone who has more that three accounts. In other words each person with three should only have three acounts listed not 7 like what I am getting in my sql statement

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-13 : 07:41:24

quote:

select c.cust_id as CustomerID, c.lname as Lastname,
c.fname as Firstname, c.mi as mi,
c.addr_1 as Address1, c.addr_2 as address2, c.city as City,
c.state as State, c.zip as Zipcode, c.ssn as SSN,
a.acct_id as AccountID, ad.acct_name as AccountType
from customer c, account a, account_descn ad
where (ad.acct_type = a.acct_type) and c.cust_id in
(select a.cust_id from account a
group by a.cust_id having COUNT(a.cust_id) > 2)

First, look into using INNER JOIN syntax -- that's ANSI standard and easier to read than using WHERE's to join tables.

When you translate the above to using INNER JOINs, you will see there is no join between the "account" table and the "account_descn" table -- which is why you are getting too many records.

So, either rewrite the SQL using INNER JOIN syntax or add to your WHERE clause to include a join between "account" and "account_descn".

In case you have no clue what I am talking about :

Change:

SELECT blah blah
FROM A,B
WHERE A.ID = B.ID AND
B.Value > 0

To:

SELECT blah blah
FROM A
INNER JOIN B
ON A.ID = B.ID
WHERE
B.Value > 0

The idea is to indicate relationships between tables very clearly, and to seperate the declarations of those relationships from filtering criteria -- which is left in the WHERE clause. Without the inner joins, everything is all mushed together in the WHERE clause and things get confusing.

- Jeff

Edited by - jsmith8858 on 01/13/2003 08:15:39

mr_mist
Grunnio

1870 Posts

Posted - 2003-01-13 : 09:27:28

I think that the problem is that you are not joining your customers to the accounts. You are selecting every customer with more than two accounts and joining accounts to their descriptions but you are just getting the cross join of all the accounts for each customer in the list. You need to include the join on whatever fields link customers to their accounts. Probably accountID or something but I don't know without the DDL.

-------
Moo.

gmcmenimen
Starting Member

3 Posts

Posted - 2003-01-13 : 10:00:06

thanks for the help. I did have three tables that have the account id's for each user and that gave me the data that I needed without getting extra account numbers. I also ended up using the inner join on all the account table with the account_descn table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources