SQL question

[DarkManX]

im doing some basic sql stuff for my database management question and i created the following quiry and am getting duplicate results:

select distinct bank_customer.customer_fname, bank_customer.Customer_lname, bank_account.bank_account_id,
bank_account.bank_account_type
from bank_customer, bank_account
order by Customer_lname;

there are 5 customers, and in the output im getting the names in order, but each name appears 5 times with 1 of each account number, can anyone help me isolate that??

thanks.

 

[her209]

It has to your with your distinct keyword. I'm just guessing here.

 

[tfinch2]

Originally posted by: her209
It has to your with your distinct keyword. I'm just guessing here.

No it has to do with selecting from multiple tables without a join.

EDIT: and wrong forum

 

[Evadman]

remember, distinct means that one of the columns must be unique or it will be excluded. assuming you want one of the account numbers, and that account_id is the account number field, you need to group by and take a min, max or something of the account_id field. You are also not joining on the tables, which means you are doing a cross join. This will duplicate all the rows form each table. So if each table has 10 rows, you will get 100 records, with every possible permutation.

 

[Markbnj]

Forget distinct for a moment. It's wrong/unneeded in this context anyway. I suspect you were using it to try to fix the problem. tfinch and Evadman gave the correct answer. I'll give an example.

You properly have two tables: customer and account. Some piece of information in both tables relates the records to each other, else you have no way of knowing which account belongs to which customer. This is the essence of what relational databases do. In bank account schemas the account number is unique, and makes a fine relationship "key" for a simple example.

table account
  account_num
  account_type
  balance
  etc.

table customer
  account_num
  lname
  fname
  address
  etc.

In this simple example the join tfinch referred to would be made on account_num...

SELECT a.account_num, a.account_type, a.balance, b.lname, b.fname, b.address FROM
account a, customer b WHERE a.account_num = b.account_num

The WHERE clause is a generic filtering mechanism, but in this case it is used to restrict the rows returned from each table to those where the condition is true: a join.

This design is not really correct because it repeats all the information from the customer table for each account relationship. A better schema would be...

table account
  account_num
  customer_id
  account_type
  balance
  etc.

table customer
  customer_id
  lname
  fname
  address
  etc.

In this case you can have multiple account records pointing to one customer.

SELECT a.account_num, a.account_type, a.balance, b.lname, b.fname, b.address FROM
account a, customer b WHERE a.customer_id = b.customer_id

 

[CTho9305]

The WHERE clause is a generic filtering mechanism, but in this case it is used to restrict the rows returned from each table to those where the condition is true: a join.

On better RDMSes that support "join on", using "join on" would be higher-performance, wouldn't it?

 

[Markbnj]

Originally posted by: CTho9305

The WHERE clause is a generic filtering mechanism, but in this case it is used to restrict the rows returned from each table to those where the condition is true: a join.

On better RDMSes that support "join on", using "join on" would be higher-performance, wouldn't it?

I can't speak for other platforms, but I don't think there is any difference in SQL Server between explicit and implicit joins in terms of performance.

Edit: I'm no DBA so it's quite possible I have no clue.

 

[Evadman]

Originally posted by: Markbnj
I can't speak for other platforms, but I don't think there is any difference in SQL Server between explicit and implicit joins in terms of performance.

There is not in most real databases. SQL Server will automatically change a where clause to an inner join wherever possible.

For example if you write this:
Select
*
from
table1, table2
where
table1.field1=table2.field1
and table1.field2= 'something'

It will be translated to this:
Select
*
from
table1 inner join table2 on table1.field1=table2.field1
where
table1.field2= 'something'