StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to combine results from multiple tables with different columns?
text
Body
copied!<p>I have several tables with different numbers and types of columns, and a single column in common.</p> <pre><code>+--------+---------+------------+-------------+ | person | beardID | beardStyle | beardLength | +--------+---------+------------+-------------+ +--------+-------------+----------------+ | person | moustacheID | moustacheStyle | +--------+-------------+----------------+ </code></pre> <p>I want to fetch all the results that match a given value of the shared column. I can do it using multiple select statements like this:</p> <pre><code>SELECT * FROM beards WHERE person = "bob" </code></pre> <p>and</p> <pre><code>SELECT * FROM moustaches WHERE person = "bob" </code></pre> <p>But this requires multiple mysql API calls, which seems inefficient. I was hoping I could use UNION ALL to get all the results in a single API call, but UNION requires that the tables have the same number and similar type of columns. I could write a SELECT statement that would manually pad the results from each table by adding columns with NULL values, but that would quickly get unmanageable for a few more tables with a few more columns.</p> <p>I'm looking for a result set roughly like this:</p> <pre><code>+--------+---------+------------+-------------+-------------+----------------+ | person | beardID | beardStyle | beardLength | moustacheID | moustacheStyle | +--------+---------+------------+-------------+-------------+----------------+ | bob | 1 | rasputin | 1 | | | +--------+---------+------------+-------------+-------------+----------------+ | bob | 2 | samson | 12 | | | +--------+---------+------------+-------------+-------------+----------------+ | bob | | | | 1 | fu manchu | +--------+---------+------------+-------------+-------------+----------------+ </code></pre> <p>Is there a way to achieve this that's fast and maintainable? Or am I better off running a separate query for each table?</p> <p><strong>Clarification:</strong></p> <p>I'm not looking for a cartesian product. I don't want a row for every combination of beard-and-moustache, I want a row for every beard and a row for every moustache.</p> <p>So if there are 3 matching beards and 2 matching moustaches I should get 5 rows, not 6.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload