Count Problems

[monsjic]

I have data like below:

TABLE 1 TABLE 2
Cust #: Inv #: Cust #: Inv #:
A 1 A 1
A 1

I want to get a count of matches on Cust # and Inv #
SELECT Count('x') AS COUNT
FROM Table1 INNER JOIN Table2 ON (Table1.C# = Table2.I#) AND (Table1.C# = Table2.I#);

But I want it to return the answer of 1 for the above sample data. The SQL above returns the answer of 2.

Does anyone know how to get my desired results?

Thanks

 

[rac2]

Could you provide more variety in your example. Include some rows that should not be counted, repeat values that should give counts of 1, 2, 3, etc.

 

[tlbroadbent]

Here is one solution.

Select
q.[Cust#],
q.[Inv#],
Count(*) As Cnt
From
(Select Distinct t1.[Cust#], t1.[Inv#]
From Table1 As t1
Join Table2 As t2
On t1.[Cust#]=t2.[Cust#]
And t1.[Inv#]=t2.[Inv#]) As q
Group By q.[Cust#], q.[Inv#] Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm