Re: Selecting lowest foreign record for each primary key;

Hello again,

Randy DeWoolfson was kind enough to email this solution, so I thought I'd post it for everyone else's benefit

    the fancy term is called: correlated subquery

    select distinct c.car_reg, d.name
    from car c, driver d, car_to_driver cd     where c.car_id = cd.car_id
    and d.driver_id in
      (select min(driver_id) from car_to_driver where car_id = c.car_id)

    not tested, off the top of my head...     hope it helps
    Randy

Thanks Randy.

Matt Warner <matt.warner_at_didio.com.NOSPAM> wrote in message news:8ngs5n$dpl$1_at_newsreaderm1.core.theplanet.net...
> Hi,
>
> I have two tables which are linked by an intermediary table, e.g.
>
> Cars = Car_Id, Car_Reg
>
> Drivers = Driver_Id, Driver_Surname
>
> Car_To_Driver = Join_Id, Car_Id, Driver_Id
>
> Each car can have multiple drivers and each driver can have multiple cars.
>
> How can I create a query that will return the Driver with the lowest
> Driver_Id for each Car?
>
> In Access I used to use the First() function to acheive this.
>
> Thanks,
>
> Matt.
>
>
Received on Thu Aug 17 2000 - 13:57:39 CDT