SQL Query Join not supported?

Question

I have a series of 4 tables that I need to loop through and pull information to make a new table in an access database:

SELECT *
  FROM tbl_f
 INNER JOIN( SELECT P, Area
               FROM tbl_l
              UNION ALL
             SELECT P, Area
               FROM tbl_m
              UNION ALL
             SELECT P, Area
               FROM tbl_w) ON tbl_f.P;

My Code includes:

Dim strSQL as string 

strSQL = "SELECT * FROM tbl_f inner JOIN( SELECT P, Area FROM tbl_l UNION ALL SELECT P, Area FROM tbl_m UNION ALL SELECT P, Area FROM tbl_w) ON tbl_f.P;"

I keep getting an error that the JOIN is not supported. I've used joins this way before, but any ideas?

You are not joining tbl_f to anything. You need to alias the sub query and join on that. ....SELECT P, Area FROM tbl_w) as q ON tbl_f.P=q.p; — Nathan_Sav
– Nathan_Sav, Commented Sep 7, 2021 at 13:49

Ankit Bajpai · Accepted Answer · 2021-09-07 13:50:53Z

3

Your join syntax is not correct. Your updated query should look alike -

SELECT *
  FROM tbl_f
 INNER JOIN( SELECT P, Area
               FROM tbl_l
              UNION ALL
             SELECT P, Area
               FROM tbl_m
              UNION ALL
             SELECT P, Area
               FROM tbl_w) T ON (tbl_f.P = T.P);

answered Sep 7, 2021 at 13:50

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DaveWuzHere · Accepted Answer · 2021-09-07 13:52:32Z

-1

The bottom line with the "on" is your problem.

Missing an "=" along with whatever you are joining on:

FROM tbl_w) ON tbl_f.P = SOMETHING

Maybe consider an outer join if you want to include nulls for blank rows

answered Sep 7, 2021 at 13:52

DaveWuzHere

2852 silver badges9 bronze badges

Collectives™ on Stack Overflow

SQL Query Join not supported?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related