**Need help on Join Syntax error**

[pradeepkumar089]

Hi All,

I have below SQL code which is showing me error when i try to run it and it says Syntax error in JOIN operation.

Could someone review the below SQL code and advise why its showing error and also please advise how to fix it.

SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], AR.[ACCOUNT NAME], AR.[GFCID NAME], AR.FLD1_DESC
FROM CSDMASTER LEFT JOIN AR ON CSDMASTER.[Feed id] = AR.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], AT.[ACCOUNT NAME], AT.[GFCID NAME], AT.FLD1_DESC
FROM CSDMASTER LEFT JOIN AT ON CSDMASTER.[Feed id] = AT.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], AU.[ACCOUNT NAME], AU.[GFCID NAME], AU.FLD1_DESC
FROM CSDMASTER LEFT JOIN AU ON CSDMASTER.[Feed id] = AU.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], BR.[ACCOUNT NAME], BR.[GFCID NAME], BR.FLD1_DESC
FROM CSDMASTER LEFT JOIN BR ON CSDMASTER.[Feed id] = BR.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], CA.[ACCOUNT NAME], CA.[GFCID NAME], CA.FLD1_DESC
FROM CSDMASTER LEFT JOIN CA ON CSDMASTER.[Feed id] = CA.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], CH.[ACCOUNT NAME], CH.[GFCID NAME], CH.FLD1_DESC
FROM CSDMASTER LEFT JOIN CH ON CSDMASTER.[Feed id] = CH.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], CL.[ACCOUNT NAME], CL.[GFCID NAME], CL.FLD1_DESC
FROM CSDMASTER LEFT JOIN CL ON CSDMASTER.[Feed id] = CL.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], CO.[ACCOUNT NAME], CO.[GFCID NAME], CO.FLD1_DESC
FROM CSDMASTER LEFT JOIN CO ON CSDMASTER.[Feed id] = CO.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], DE.[ACCOUNT NAME], DE.[GFCID NAME], DE.FLD1_DESC


FROM CSDMASTER LEFT JOIN DE ON CSDMASTER.[Feed id] = DE.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], ES.[ACCOUNT NAME], ES.[GFCID NAME], ES.FLD1_DESC
FROM CSDMASTER LEFT JOIN ES ON CSDMASTER.[Feed id] = ES.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], FR.[ACCOUNT NAME], FR.[GFCID NAME], FR.FLD1_DESC
FROM CSDMASTER LEFT JOIN DE ON CSDMASTER.[Feed id] = FR.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], GB.[ACCOUNT NAME], GB.[GFCID NAME], GB.FLD1_DESC
FROM CSDMASTER LEFT JOIN GB ON CSDMASTER.[Feed id] = GB.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], GR.[ACCOUNT NAME], GR.[GFCID NAME], GR.FLD1_DESC
FROM CSDMASTER LEFT JOIN GR ON CSDMASTER.[Feed id] = GR.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], HK.[ACCOUNT NAME], HK.[GFCID NAME], HK.FLD1_DESC
FROM CSDMASTER LEFT JOIN HK ON CSDMASTER.[Feed id] = HK.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], ID.[ACCOUNT NAME], ID.[GFCID NAME], ID.FLD1_DESC
FROM CSDMASTER LEFT JOIN ID ON CSDMASTER.[Feed id] = ID.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], IND.[ACCOUNT NAME], IND.[GFCID NAME], IND.FLD1_DESC
FROM CSDMASTER LEFT JOIN IND ON CSDMASTER.[Feed id] = IND.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], IT.[ACCOUNT NAME], IT.[GFCID NAME], IT.FLD1_DESC
FROM CSDMASTER LEFT JOIN IT ON CSDMASTER.[Feed id] =IT.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], KR.[ACCOUNT NAME], KR.[GFCID NAME], KR.FLD1_DESC
FROM CSDMASTER LEFT JOIN KR ON CSDMASTER.[Feed id] = KR.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], LU.[ACCOUNT NAME], LU.[GFCID NAME], LU.FLD1_DESC
FROM CSDMASTER LEFT JOIN LU ON CSDMASTER.[Feed id] = LU.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], MX.[ACCOUNT NAME], MX.[GFCID NAME], MX.FLD1_DESC
FROM CSDMASTER LEFT JOIN MX ON CSDMASTER.[Feed id] = MX.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], MY.[ACCOUNT NAME], MY.[GFCID NAME], MY.FLD1_DESC
FROM CSDMASTER LEFT JOIN MY ON CSDMASTER.[Feed id] = MY.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], PE.[ACCOUNT NAME], PE.[GFCID NAME], PE.FLD1_DESC
FROM CSDMASTER LEFT JOIN PE ON CSDMASTER.[Feed id] = PE.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], PT.[ACCOUNT NAME], PT.[GFCID NAME], PT.FLD1_DESC
FROM CSDMASTER LEFT JOIN PT ON CSDMASTER.[Feed id] = PT.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], RO.[ACCOUNT NAME], RO.[GFCID NAME], RO.FLD1_DESC
FROM CSDMASTER LEFT JOIN RO ON CSDMASTER.[Feed id] = RO.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], RU.[ACCOUNT NAME], RU.[GFCID NAME], RU.FLD1_DESC
FROM CSDMASTER LEFT JOIN RU ON CSDMASTER.[Feed id] = RU.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], SE.[ACCOUNT NAME], SE.[GFCID NAME], SE.FLD1_DESC
FROM CSDMASTER LEFT JOIN SE ON CSDMASTER.[Feed id] = SE.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], SG.[ACCOUNT NAME], SG.[GFCID NAME], SG.FLD1_DESC
FROM CSDMASTER LEFT JOIN SG ON CSDMASTER.[Feed id] = SG.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], TH.[ACCOUNT NAME], TH.[GFCID NAME], TH.FLD1_DESC
FROM CSDMASTER LEFT JOIN TH ON CSDMASTER.[Feed id] = TH.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], TW.[ACCOUNT NAME], TW.[GFCID NAME], TW.FLD1_DESC
FROM CSDMASTER LEFT JOIN TW ON CSDMASTER.[Feed id] = TW.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], US.[ACCOUNT NAME], US.[GFCID NAME], US.FLD1_DESC
FROM CSDMASTER LEFT JOIN US ON CSDMASTER.[Feed id] = US.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], ZA.[ACCOUNT NAME], ZA.[GFCID NAME], ZA.FLD1_DESC
FROM CSDMASTER LEFT JOIN ZA ON CSDMASTER.[Feed id] = ZA.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], PE.[ACCOUNT NAME], PE.[GFCID NAME], PE.FLD1_DESC
FROM CSDMASTER LEFT JOIN PE ON CSDMASTER.[Feed id] = PE.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], PH.[ACCOUNT NAME], PH.[GFCID NAME], PH.FLD1_DESC
FROM CSDMASTER LEFT JOIN PH ON CSDMASTER.[Feed id] = PH.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], MA.[ACCOUNT NAME], MA.[GFCID NAME], MA.FLD1_DESC
FROM CSDMASTER LEFT JOIN MA ON CSDMASTER.[Feed id] = MA.[* Account Number]
UNION
SELECT CSDMASTER.[Account number], CSDMASTER.Branch, CSDMASTER.[Feed id], JP.[ACCOUNT NAME], JP.[GFCID NAME], JP.FLD1_DESC
FROM CSDMASTER LEFT JOIN JP ON CSDMASTER.[Feed id] = JP.[* Account Number];

[ssanfu]

Way too much to wade through.

Try using the first 3 unions to see if you get an error. If not, continue adding 1 union clause at a time until you get an error. Then fix the error.



You really have a field named "[* Account Number]"??
This is an extremely poor field name.

[June7]

Does the field name really have * character?

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. However, the [] should force acceptance although I encountered one situation where it did not.

Did you test SELECT statements in query builder?

Don't see anything wrong with the syntax.

Build the UNION one SELECT at a time. Test it before adding the next SELECT. Build another UNION and copy/paste from the first until you get the line that causes issue.

[John_G]

I don't see anything wrong right away either. This is only a guess, but it might be that one of your table names is infact a reserved word which will cause an error under some conditions (AT maybe?).

If you have that many tables with the same structure, one for each country, you need to re-think your database structure. Having all the records in one table, with an additional country code field, would be a much better approach, and would avoid nightmarish query scenarios such as your above. Is there a reason why you didn't or cannot do that?