Select first record in a One-to-Many relation using left join

[vanwooten]

I have two tables:

membersac :
View attachment 112136
ACtoZIP:
View attachment 112137

Trying to get dataset of membersac.area_code, membersac.AC_Count and ANY 1 zip from ACto Zip. I found the following code but I keep getting "Syntax error in query expression." Any ideas?


SELECT membersac.Area_Code, membersac.AC_Count, ACtoZIP.zip
FROM membersac
LEFT JOIN ACtoZIP
ON ACtoZIP.area_code = membersac.Area_Code
AND ACtoZIP.ID = (SELECT TOP 1 ID
FROM ACtoZIP
WHERE ACtoZIP.area_code = membersac.Area_Code);

 

[vanwooten]

I have two tables:

membersac :
View attachment 112136
ACtoZIP:
View attachment 112137

Trying to get dataset of membersac.area_code, membersac.AC_Count and ANY 1 zip from ACto Zip. I found the following code but I keep getting "Syntax error in query expression." Any ideas?


SELECT membersac.Area_Code, membersac.AC_Count, ACtoZIP.zip
FROM membersac
LEFT JOIN ACtoZIP
ON ACtoZIP.area_code = membersac.Area_Code
AND ACtoZIP.ID = (SELECT TOP 1 ID
FROM ACtoZIP
WHERE ACtoZIP.area_code = membersac.Area_Code);

 

[welshgasman]

If you paste that into the sql window, it should show you where?

Other than that, get the subquery working first, then place that as criteria in the design window.
Do you actually need the join?, as the criteria is the same?

 

[welshgasman]

This might be your issue?

AND ACtoZIP.ID =

should be (perhaps)

WHERE ACtoZIP.ID IN

I always have to play with SQL design, I cannot write anything but simple queries directly.