syntax help on join sql (1 Viewer)

[dj59]

The syntax is not correct on this sql to use in MS Access.
Can you help? I think I need brackets around the joins and/or 'inner' before the join....

select  p.sw_index_id, [P.LAST_NAME] & ', ' & [P.FIRST_NAME] AS PERSONNAME1,
cp.cnty_cd, p.ssn, p.last_chgd_dt, an.gender_cd, p.birth_dt, p.gender_cd, p.pmi,
sp1.ms_pmi, an.birth_dt
from salti_person p
join sw_alias_name an on an.south_index_id = p.south_index_id
join sw_county_pid cp on p.person_id = cp.cnty_person_id and cp.cnty_cd = '11'
join sw_south_person sp1 on p.south_index_id = sp1.south_index_id
where p.gender_cd <> an.gender_cd
and p.south_index_id = an.south_index_id
and p.birth_dt = sp1.birth_dt
order by p.south_index_id

 

[jdraw]

Multi table general

SELECT ...
FROM ((origintable
JOIN jointable1 ON ...)
JOIN jointable2 ON ...)
JOIN jointable3 ON ...

This may be helpful.

 

[dj59]

Yes. That worked. thank you.

 

[ButtonMoon]

Access places some restrictions on what you can do in an ON clause and requires you to add brackets around JOINs. You may find it easier to use the alternative and more "standard" SQL comma/WHERE syntax, which doesn't have the same restrictions. I have never understood quite why Access requires such an awkward syntax when you use JOIN keywords but not when you do exactly the same thing in the WHERE clause.

SELECT  p.sw_index_id, [P.LAST_NAME] & ', ' & [P.FIRST_NAME] AS PERSONNAME1,
cp.cnty_cd, p.ssn, p.last_chgd_dt, an.gender_cd, p.birth_dt, p.gender_cd, p.pmi,
sp1.ms_pmi, an.birth_dt
FROM salti_person p, sw_alias_name an, sw_county_pid cp, sw_south_person sp1
WHERE an.south_index_id = p.south_index_id
AND p.person_id = cp.cnty_person_id and cp.cnty_cd = '11'
AND p.south_index_id = sp1.south_index_id
AND p.gender_cd <> an.gender_cd
AND p.south_index_id = an.south_index_id
AND p.birth_dt = sp1.birth_dt
ORDER BY p.south_index_id;