Need help with FULL JOIN 3 tables

[vintox]

i am trying to do a full join for 3 tables

i know how to make full join for 2 tables using left,right joins and union all but when i tried to join the third table i got some errors

 

i tried this one:

SELECT gcap,tcap FROM
(SELECT type.caption as tcap,genre.caption as gcap
FROM   type 
       LEFT JOIN genre 
          ON type.id = genre.id
UNION
SELECT type.caption as tcap,genre.caption as gcap
FROM   type
       RIGHT JOIN genre
          ON type.id = genre.id 
WHERE  type.id IS NULL 
)a
LEFT JOIN category ON category.id = type.id

 

Any help is welcome

Thanks in advance

Vintox

[DavidAM]

SELECT gcap, tcap 
FROM (...) a LEFT JOIN category ON category.id = type.id

You are referring to type.id when there is no table "type" in the query, so no "id" field to check.  I'm guessing you want to return the "id" column in the psuedo table

(SELECT type.id, type.caption as tcap,genre.caption as gcap
FROM   type
       LEFT JOIN genre
          ON type.id = genre.id
UNION
SELECT type.id, type.caption as tcap,genre.caption as gcap
FROM   type
       RIGHT JOIN genre
          ON type.id = genre.id
WHERE  type.id IS NULL )

( or maybe that second type.id should be genre.id? )

 

and then JOIN ON "a.id" in the main SELECT.