How to combine results from multiple tables with different columns?

Question

I have several tables with different numbers and types of columns, and a single column in common.

+--------+---------+------------+-------------+
| person | beardID | beardStyle | beardLength |
+--------+---------+------------+-------------+

+--------+-------------+----------------+
| person | moustacheID | moustacheStyle |
+--------+-------------+----------------+

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:

SELECT * FROM beards WHERE person = "bob"

and

SELECT * FROM moustaches WHERE person = "bob"

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.

I'm looking for a result set roughly like this:

+--------+---------+------------+-------------+-------------+----------------+
| person | beardID | beardStyle | beardLength | moustacheID | moustacheStyle |
+--------+---------+------------+-------------+-------------+----------------+
| bob    | 1       | rasputin   | 1           |             |                |
+--------+---------+------------+-------------+-------------+----------------+
| bob    | 2       | samson     | 12          |             |                |
+--------+---------+------------+-------------+-------------+----------------+
| bob    |         |            |             | 1           | fu manchu      |
+--------+---------+------------+-------------+-------------+----------------+

Is there a way to achieve this that's fast and maintainable? Or am I better off running a separate query for each table?

Clarification:

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.

So if there are 3 matching beards and 2 matching moustaches I should get 5 rows, not 6.

Answer 1

Join on person....

I.e.

Select t1.(asterix), t2.(asterix) FROM beards t1 INNER JOIN moustaches t2 On t2.person = t1.person

Answer 2

SELECT *
FROM   beards
       JOIN moustaches
         ON moustaches.person = beards.person
WHERE  person = "bob"  

Answer 3

Use:

SELECT b.person, 
       b.beardid,
       b.beardstyle,
       b.beardlength,
       NULL AS mustacheid,
       NULL AS moustachestyle
  FROM BEARDS b
UNION ALL
SELECT m.person,
       NULL,
       NULL,
       NULL,
       m.mustacheid,
       m.moustachestyle
  FROM MOUSTACHES m

Answer 4

I had fun with this, not sure it's entirely manageable with what more you have to add, but it accomplished the goal.

create table beard (
person varchar(20)
,beardID int
,beardStyle varchar(20)
,beardLength int )

create table moustache(
person varchar(20)
,moustacheID int
,moustacheStyle varchar(20))


insert into beard 
select 'bob', 1, 'rasputin', 1
union select 'bob', 2, 'samson', 12

insert into moustache
select 'bob', 1, 'fu manchu'

declare @facialhair table (
person varchar(20)
,beardID int
,beardStyle varchar(20)
,beardLength int
,moustacheID int
,moustacheStyle varchar(20))

declare @i int
declare @name varchar(20)

set @name = 'bob'
set @i = (select COUNT(*) from beard where person = @name)
        + (select COUNT(*) from moustache where person = @name) 

print @i

while @i > 0
    begin 
        insert into @facialhair (person, beardID, beardStyle, beardLength)
        select person, beardID, beardStyle, beardLength
        from beard
        where person = @name
    set @i = @i-@@ROWCOUNT

        insert into @facialhair (person, moustacheID, moustacheStyle)
        select person, moustacheID, moustacheStyle
        from moustache
        where person = @name
    set @i = @i-@@ROWCOUNT
    end

select *
from @facialhair

Answer 5

this should be working fine:

SELECT * FROM `beards` b LEFT OUTER JOIN `mustaches` ON (0) WHERE  person = "bob"
UNION ALL
SELECT * FROM `beards` b RIGHT OUTER JOIN `mustaches` ON (0) WHERE  person = "bob"

you don't have to handle the columns by yourself. the left and right outer join do this job. unfortunately mysql doesn't have a full join. that's why you have to do it this way with a union

SELECT * FROM `customer` b LEFT OUTER JOIN `charges` ON (0) LEFT OUTER JOIN `day` ON (0)
UNION
SELECT * FROM `customer` b RIGHT OUTER JOIN `charges` ON (0) LEFT OUTER JOIN `day` ON (0)
UNION
SELECT * FROM `customer` b LEFT OUTER JOIN `charges` ON (0) RIGHT OUTER JOIN `day` ON (0)

this is a local test i made

Answer 6

Hi, I think you would be better by making queries for data in each table.

One of other possibilities is to concatenate data from all columns into one big string (you could choose some sign to separete column's values), then you should be able to use union all clause to combine results from each query - but then you will have to parse each row.. And data types will be lost.