SQL How to get FULL OUTER JOIN where primary key/field is a specific value in all tables

Question

Can anyone help me to get this query working or an alternative to do the same? Each table has the primary key field called 'Field', every other field is different but I'd like to join these fields and get a return of all columns in the 'value' row of each table all together where Field = 'value'.

Query:

SELECT A.*, B.*, C.* 
FROM
(
 (SELECT * FROM Table1 WHERE Field = 'value') A 
 FULL OUTER JOIN 
 (SELECT * FROM Table2 WHERE Field = 'value') B on A.Field = B.Field 
 FULL OUTER JOIN 
 (SELECT * FROM Table3 WHERE Field = 'value') C on A.Field = C.Field
);

Tables:

Table1

Field----------Col1------------Col2
value------------1---------------2

Table2

Field----------Col3------------Col4
value------------3---------------4

Table3

Field----------Col5------------Col6
value------------5---------------6

Desired Result:

Field------Col1------Col2------Col3------Col4------Col5------Col6
value-------1------------2----------3----------4-----------5----------6

I'm using C#, any other query works fine so I'm sure it's a syntax issue or something. The error message I get is

"IErrorInfo.GetDescription failed with E_FAIL(0x80004005)"

I'd appreciate any help

https://stackoverflow.com/questions/19615177/how-do-i-write-a-full-outer-join-query-in-access — HLGEM, Jul 24 '17 at 14:40

score 1 · Answer 1 · answered Jul 24 '17 at 14:21

You have an extra set of parens in the FROM clause. I would suggest:

SELECT A.*, B.*, C.* 
FROM (SELECT * FROM Table1 WHERE Field = 'value'
     ) A FULL OUTER JOIN 
     (SELECT * FROM Table2 WHERE Field = 'value'
     ) B 
     ON A.Field = B.Field FULL OUTER JOIN 
     (SELECT * FROM Table3 WHERE Field = 'value'
     ) C 
     ON C.Field = COALESCE(A.Field, B.Field)

Essentially you want to do an "outer cross join", but that is not a SQL operator.

You might also phrase the query like this:

select a.*, b.*, c.*
from (select 'value' as field) x left join
     a
     on a.field = x.field left join
     b
     on b.field = x.field left join
     c
     on c.field = x.field;

Note some databases require a FROM clause for the first subquery, so the syntax might vary slightly depending on the database.

Thanks for your suggestions Gordon I'll give them a shot now! — ghostcoder, Jul 24 '17 at 14:26
I just discovered, and as HLGEM pointed out, Access doesn't support full outer joins — ghostcoder, Jul 24 '17 at 14:41

SQL How to get FULL OUTER JOIN where primary key/field is a specific value in all tables

1 Answers1