It won't work.. Firebird will throw an error such as
SQL error code = -204.
Ambiguous field name between table a and table b

try this

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

--
Alan J Davies
Aldis


Quoting Larry Johnson <larry@...>:

> 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]
>
>