dinol.softedge wrote:

> My problem is that I have a table that I am using to populate another.
> The table is TBL_JOB_CARD_INVENTORY and I'm trying to populate
> TBL_INVENTORY by finding all records in TBL_JOB_CARD_INVENTORY that do
> not have matching records in TBL_INVENTORY. SO far I have come up
> with the following statement. Problem is that some of the fields have
> differing data for each unique Inventory_ID. I am happy just to take
> the first instance of each record for each unique Inventory_ID. Any
> help would be greatly appreciated. Many thanks

If I understand you correctly, what you're doing is this:

insert into T1 (pk, <otherstuff>)
select distinct T2.pk, <T2.otherstuff>
from T2
left outer join T1 on <pk>
where T1.pk is null

This would indeed work if all <otherstuff> is identical for each pk. But
if I understand your text correctly, your problem is thta <otherstuff>
appears with varying different values for each pk in T2, resulting in
primary key violations.

If this is the case, then this could solve your problem:

insert into T1 (pk, other1, other2, ..., otherK)
select T2.pk, min(T2.other1), min(T2.other2), ..., min(T2.otherK)
from T2
left outer join T1 on <pk>
where T1.pk is null
group by T2.pk, T2.other1, T2.other2, ..., T2.otherK

The problem here is that you will receive a mix of <otherstuff> values
picked from various different T2 records. It will pick, per column, the
smallest value. If you need to pick all <otherstuff> from the same T2
record, but don't care which one, then you need a different approach. I
haven't really analyzed the other suggestions.

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64