One to many join

[Acceris]

There are 3 records in table A.

id custid
1 1234
2 2345
3 3456

and 6 records in table B.

id date
1 10/01/05
2 10/01/05
2 10/03/05
3 09/30/05
3 10/01/05
3 10/02/05

The following SQL statement returns 5 records:

select a.id, a.custid from a inner join b on a.id = b.id
where b.date >= '10/01/05' and b.date <= '10/03/05'
group by a.id

Here is the return from the above query:

id custid
1 1234
2 2345
2 2345
3 3456
3 3456

I only wanted 3 records. Can someone please revise my SQL statement?

Thanks,
Dan-Yeung

[Phil Weber]

SELECT DISTINCT a.id, a.custid
FROM a INNER JOIN b ON a.id = b.id
WHERE b.date>= '10/1/2005' AND b.date<= '10/3/2005'

[Acceris]

Hi Phil,

I tried both distinct and group by. As long as the b.date is different, it retures two records fore the same id. Can you please help?

Thanks.
Dan-Yeung

[Phil Weber]

I cannot reproduce that behavior. You're asking for distinct combinations of a.id and a.custid; b.date should not make any difference unless you're including it in the SELECT clause.