CODEWITHSUNDEEP
phpmysql
twigg
twigg

Reputation: 3993

mySQL full outer join with a inner join?

I'm not sure of the best way to go about this or even if it’s possible. Let’s say I have 4 tables, categories, documents, other_documents, more_documents only documents have a category the others don’t. I'm using MySQL and PHP. I understand a full outer join is not an option in mysql and must be worked around using left and right joins.

I can get all the documents and inner join the categories on them where the id’s match, great!

Now I have the above data set I also want other_documents and more_documents ‘adding’ to the bottom of the above results sets and null filling in where the categories don’t line up.

Say in the more_documents table there is a unique column that the documents and other_documents tables do not have. An example, phil and dave submit data into the documents and other_documents tables and jon only submits data into the more_documents table so the results would read:

Id - Title - publisher - category - unique more_documents column

1 - doc1 - dave - global - null

2 - doc2 - phil - hr - null

3 - doc3 - dave - operations - null

4 - doc4 - dave - global - null

5 - doc5 - jon - global - 12345

6 - doc6 - jon - hr - 12345

Upvotes: 0

Views: 153

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You want to structure the query with a "driver" subquery:

select driver.Id, driver.Title, driver.publisher, c.category
from ((select id, categoryId, Title, publisher
       from documents
      ) union
      (select id, NULL, Title, publisher
       from more_documents
      ) union
      (select id, NULL, Title, publisher
       from other_documents
      )
     ) driver left outer join
     category c
     on driver.category_id = c.categoryId

Note that union is used in the driver subquery because it removes duplicates.

You may need additional joins back to the document tables to pick up fields that are only in one table.

Upvotes: 1

Related Questions