title: SQL join issue - joining two related queries (Full Outer Join)
body: <p>I have been battling with this for days. I'm no expert with SQL and have managed to create a few queries, however I'm having a real job joing the queries up.</p>
  
  <p>For example, I want to join the following two queries:</p>
  
  <pre><code>SELECT publication_id AS Pure_Publication_Id, caa.person_id, 
      caa.name_first_name AS Primary_Author_First_Name, 
      caa.name_last_name AS Primary_Author_Last_Name
  FROM pure.classified_author_assoc caa
      LEFT OUTER JOIN Pure.Publication
          ON Caa.Publication_Id = Publication.Id
  WHERE Caa.Person_Id  IS NOT NULL
      AND publication.id IN
      (
          SELECT DISTINCT pure.publication_project_assoc.publication_id
          FROM Pure.project
              JOIN Pure.Publication_project_assoc
                  ON project.id = Pure.Publication_project_assoc.project_id
          WHERE pure.project.source_id IN ('XEP378', 'XES049', 'YAH001')
      )
  GROUP BY publication_id, caa.person_id, caa.name_first_name, caa.name_last_name;
  </code></pre>
  
  <p>and</p>
  
  <pre><code>SELECT publication.id AS Pure_Publication_Id
      COUNT (person_id) AS "NUMBER_OF_AUTHORS", 
      CASE WHEN COUNT (person_id) &gt; 1 THEN 'Yes' 
          else 'No' END AS Additional_Authors
  FROM pure.classified_author_assoc caa
      LEFT OUTER JOIN Pure.Publication
          ON Caa.Publication_Id = Publication.Id
  WHERE Caa.Person_Id  IS NOT NULL
      AND publication.id IN
      (
          SELECT DISTINCT pure.publication_project_assoc.publication_id
          FROM Pure.project
              JOIN Pure.Publication_project_assoc
                  ON project.id = Pure.Publication_project_assoc.project_id
          WHERE pure.project.source_id IN ('XEP378', 'XES049', 'YAH001')
      )
  GROUP BY publication.id;
  </code></pre>
  
  <p>I cannot combine them in a single query (I don't think) as the first query would only return Number_of_authors as one. However, when I try to run the query will a full join in the middle as below, I get a SQL command not ended properly error at the end of the first sql statement and I'm not sure what the join should be on at the end.</p>
  
  <pre><code>SELECT publication_id AS Pure_Publication_Id, caa.person_id, 
      caa.name_first_name AS Primary_Author_First_Name,
      caa.name_last_name AS Primary_Author_Last_Name
  FROM pure.classified_author_assoc caa
      LEFT OUTER JOIN Pure.Publication
          ON Caa.Publication_Id = Publication.Id
  WHERE Caa.Person_Id  IS NOT NULL
      AND publication.id IN
      (
          SELECT DISTINCT pure.publication_project_assoc.publication_id
          FROM Pure.project
              JOIN Pure.Publication_project_assoc
                  ON project.id = Pure.Publication_project_assoc.project_id
          WHERE pure.project.source_id IN ('XEP378', 'XES049', 'YAH001')
      )
  GROUP BY publication_id, caa.person_id, caa.name_first_name, caa.name_last_name
  FULL OUTER JOIN
  (
      SELECT publication.id AS Pure_Publication_Id, 
          COUNT (person_id) AS "NUMBER_OF_AUTHORS", 
          CASE WHEN COUNT (person_id) &gt; 1 THEN 'Yes' 
              else 'No' END AS Additional_Authors
      FROM pure.classified_author_assoc caa
          LEFT OUTER JOIN Pure.Publication
              ON Caa.Publication_Id = Publication.Id
      WHERE Caa.Person_Id  IS NOT NULL
          AND publication.id IN
          (
              SELECT DISTINCT pure.publication_project_assoc.publication_id
              FROM Pure.project
                  JOIN Pure.Publication_project_assoc
                      ON project.id = Pure.Publication_project_assoc.project_id
              WHERE pure.project.source_id IN ('XEP378', 'XES049', 'YAH001')
          )
  )
  GROUP BY publication.id
  ON ****;
  </code></pre>
  
  <p>I have tried this every way I can think of and know it should be something simple. Can anybody help please?</p>