Matt Clark wrote:

> This may turn out to be a little cryptic so I'll start simple and work
> up if anyone suggests something I can't actually do.
>
> I have an amalgamation of several tables. Specifically a product table
> with several detail tables that are linked by a stock code(sku). A
> couple of the tables may or may not return 1 or more matching records by
> stock code. Easy left join so far.
> eg,
> from item_history
> left join locations on item_history.sku = locations.sku
>
> problem is, if there is more than one matching row in locations, I still
> only want exactly 1 row per sku in the top level.

But which one?

> The reason for this anomaly is that there /should/ only be one location
> per sku but we aren't going to be enforcing this rule for some business
> reason so I need to be prepared to handle this.

If you using firebird, you can try to do the subselect (in select
clause) with FIRST, something like:

select colX, (select first 1 colY from locations l where l.sku = i.sku)
from item_history i;

Or, you can write a stored procedure which will filter the original
select you had in mind for duplicates.

--
Milan Babuskov
http://fbexport.sourceforge.net