You could SUM on “Unit” presumably. I’m not sure about “Code”. Maybe do COUNT on it? As long as table B has more than one row from that matches a row in A, you’re going to get multiple matches and thus rows on PatId.



Another option would be to do a subselect, but you’re only going to end up with one value for Unit and Code somehow to do what you want.



From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Alejandro Garcia
Sent: Monday, January 12, 2009 9:10 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] distinct in join



Hi! I have table A: PatId Date Country
and table B: PatId Unit Code

I need to join them by PatId with a condition in the Date field and show the results with no repeated PatIds,

This is not working:

Select DISTINCT(PatId), Date, Country, Unit, Code
from A
JOIN B ON (A.PatId=B.PatId)
where (A.Date >= 01/01/2008 and A.Date <= 31/12/2008)
order by PatId

I tried to group by and then select distinct by it's not working, how could I select distinct?
Sorry for this question that seems to be too easy but can't solve it...

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]