Seems like it shouldn''t be that difficult
-
I have two tables. The first is named tblEquipment and, for simplicity sake, it has just one column which is EquipNumber and it is the Primary Key. The second table named tblLicenses is related to tblEquipment. It has two columns named EquipNumber and LicenseNumber, which together form the Primary Key. So far, so good.
Some EquipNumbers have more than one LicenseNumber. For example, the truck might be licensed in Alaska or Washington, and also in one of the Canadian Provinces. So here's some sample data:
tblEquipment tblLicenses
C100 C100 AK12345
C200 C200 WA88888
C200 YT65432
On my Equipment List, I want to show each piece of equipment once and only once. I also want to show a LicenseNumber for the EquipNumber and I don't care which of the LicenseNumbers is selected, but I only want one of them.
TIA.........Cindy
-
Try this:
SelecttblEquipment.EquipNumber
,Licenses.LicenseNumber
fromtblEquipment
join(selectEquipNumber
,MIN(LicenseNumber)
fromtblLicenses
group byEquipNumber
) as Licenses ( EquipNumber , LicenseNumber )
on Licenses.EquipNumber = tblEquipment.EquipNumber
SQL = Scarcely Qualifies as a Language
-
Worked perfectly. Thanks a million. You're my hero.
-
select EquipNumber
, MIN(LicenseNumber) as License
from tblLicenses
group by EquipNumber
[Edit:] Should learn to type faster, damn!
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply