Query in Query

[poporacer]

I have two tables (tblRoster and tblAKA) tblRoster has the specifics on each person (ID#(unique identifier) Name, Address, Phone) tblAKA has the ID# and nicknames, sometimes more than one nickname for a person. I need a query that will join the two tables BUT I only want one entry for each person.

Here is an example of what I am trying to do:

tblRoster:
ID Name Address Phone
1 Betty 123 St. 555-123-1234
2 Sam 321 W. 555-987-1234
3 Tom 456 S. 555-456-9876

tblAKA:
ID Nick
1 Betty Boop
2 Shorty
1 Scary

And I want to get the following results:
ID Name Address Phone Nick
1 Betty 123 St. 555-123-1234 Betty Boop
2 Sam 321 W. 555-987-1234 Shorty
3 Tom 456 S. 555-456-9876

I tried a Left Join and tried to use the DISTINCT property on the ID but still got duplicates:

ID Name Address Phone Nick
1 Betty 123 St. 555-123-1234 Betty Boop
1 Betty 123 St. 555-123-1234 Scary
2 Sam 321 W. 555-987-1234 Shorty
3 Tom 456 S. 555-456-9876

I also tried something but didn't work but seemed close:


SELECT ID, Name, Address, Phone, tblAKA.Nick
FROM tblRoster
LEFT JOIN
(SELECT DISTINCT tblAKA.ID, tblAKA.Nick FROM tblAKA) on tblRoster.ID = tblAKA.ID

Any Ideas???

[poporacer]

I figured this out...Thanks