[RESOLVED] Query displays wrong

[aNubies]

Ok here's another query question and set of data.

Table1

ID | DATE

2004-0001 | 2010-01-26
2004-0001 | 2010-01-26

TABLE2

ID | SALES

2004-0001 | 1000
2004-0001 | 3000

What I want to do there is just combine them and later the second query is display the sum of sales.
So in my first query wherein combining them this is how I done it.

    SELECT A.ID, A.DATE, B.SALES
      FROM TABLE1 A
INNER JOIN TABLE2 B
	ON A.ID = B.ID

The only problem that I encountered there is that it displays 4 data, which is wrong.

ID | DATE | SALES
2004-0001 | 2010-01-26 | 1000
2004-0001 | 2010-01-26 | 3000
2004-0001 | 2010-01-26 | 1000
2004-0001 | 2010-01-26 | 3000

Any corrections in my query. The result should be:

ID | DATE | SALES
2004-0001 | 2010-01-26 | 1000
2004-0001 | 2010-01-26 | 3000

Help here. For the second query question which is displaying the sum I think I can figured it out If I can get this first query.

[FunkyDexter]

The problem is that table 1 contains two identical records. Both of these are getting joined to Table 2 so you end up with two "pairs".

The question is should table 1 have two identical records? If not then clean up your data (ie delete one of the records from table 1) and you'll get the result you want.

If, on the other hand, table 1 should contain those two identical records then you need to 'collapse' identical rows in your dataset. There are two ways of doing that:-

1 - DISTINCT : The distinct keyword only returns one of each record when they're identical:-

SELECT DISTINCT A.ID, A.DATE, B.SALES
      FROM TABLE1 A
INNER JOIN TABLE2 B
      ON A.ID = B.ID

We tend to frown on DISTINCT, though. It works and performs well but it's a bit of a blunt tool and kind hide all sorts of underlying problems with a query. It's OK for ad hoc queries but I wouldn't write it into a system.

A better aproach is...
2 - GROUP BY : The Group By clause collapses rows based on a list of fields you supply. When two records have the same values in all the fields you're grouping by they'll be collapsed into one row:-

SELECT A.ID, A.DATE, B.SALES
      FROM TABLE1 A
INNER JOIN TABLE2 B
      ON A.ID = B.ID
GROUP BY A.ID, A.DATE, B.SALES

Because you're grouping by all the fields you're selecting this is functionally identical to DISTINCT. But, because you enter the list of fields yourself, you're forced to think about how the records will collapse and make sure you've got it right.

[aNubies]

Gee thanks again FunkyDexter .