CODEWITHSUNDEEP
c#sqlms-access
ghostcoder
ghostcoder

Reputation: 1

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

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

Upvotes: 0

Views: 910

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

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.

Upvotes: 1

Related Questions