CODEWITHSUNDEEP
mysqlsql
CCSab
CCSab

Reputation: 1475

MySQL: Combine multiple disparate tables into one

I have multiple tables, say A, B, and C where B and C have a foreign key to A.

I want to write a query that will return a result set where the columns of the result set are the columns of A and B and C combined, i.e. 

A.id A.name B.id B.name C.id C.name
-----------------------------------
1    Thing  2    Bee    NULL NULL
1    Thing  NULL NULL   1    Cow

That is, essentially a combination of the results of an individual LEFT OUTER JOIN on B and C, but combined into a single result set. I am ok with there being multiple NULL columns.

EDIT: The result set will always have the entries of A, but only ONE of either B or C.

Is this possible? Or is there a better way of joining this information?

Upvotes: 2

Views: 262

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270617

Since you never want a single row to contain both B and C, you might UNION together two separate join queries, substituting NULL literals for the opposite table to get the columns to align. Each part of the UNION supplies the relationship between A->B or A->C, but must return NULL for all the columns of the opposite table. Supply a NULL literal to leave empty every column from the other table.

In the end to sort them, you can conditionally check that the B columns are NOT NULL to force the B row to sort ahead of the C row, after first ordering by A.id.

(
  SELECT 
    A.id AS a_id,
    A.name AS a_name,
    B.id AS b_id,
    B.name AS b_name,
    /* Substitute NULLs... for the other table C, for *all columns* */
    NULL AS c_id,
    NULL AS c_name,
    NULL AS other_c
    /* etc, other cols from C */
  FROM A
    LEFT JOIN B ON A.id = B.a_id
/* UNION, rather than UNION ALL  in case both tables have NULLs */
) UNION (
  SELECT
    A.id AS a_id,
    A.name AS a_name,
    /* This time substitute NULLs for B, again *all columns* */
    NULL AS b_id,
    NULL AS b_name,
    C.id AS c_id,
    C.name AS c_name
    C.other_c
  FROM A
    LEFT JOIN C ON A.id = C.a_id
)
ORDER BY 
  a_id,
  /* sort the non-null B ahead... */
  CASE WHEN b_id IS NOT NULL THEN 0 ELSE 1 END

Upvotes: 1

Related Questions