How to combine results from several tables with different columns?

Question

I have several tables with different numbers and types of columns and one column.

+--------+---------+------------+-------------+ | person | beardID | beardStyle | beardLength | +--------+---------+------------+-------------+ +--------+-------------+----------------+ | person | moustacheID | moustacheStyle | +--------+-------------+----------------+ 

I want to get all the results that match the given value of a common column. I can do this using several select statements, for example:

 SELECT * FROM beards WHERE person = "bob" 

and

 SELECT * FROM moustaches WHERE person = "bob" 

But this requires several 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 the same type of columns. I could write a SELECT statement that would manually put the results from each table, adding columns with NULL values, but that would quickly become unmanageable for multiple tables with multiple columns.

I am looking for a result set something 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 is fast and easy to maintain? Or am I better off running a separate query for each table?

Clarification:

I am not looking for a delicate product. I do not need a string for each combination of beards and mustaches, I need a string for each beard and a string for each mustache.

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

Answer 1

this should work 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 do not need to handle the columns yourself. left and right outer joins do the job. unfortunately mysql does not have a full connection. why you should do it this way with pooling

 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 did

Answer 2

Join the person ....

those.

Choose t1. (asterix), t2. (asterix) FROM beard t1 INTERNAL JOINT whiskers t2 On t2.person = t1.person

Answer 3

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

Answer 4

I had fun with this, not sure if it is completely manageable with what you need to add, but it has achieved 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

I think you will be better off making queries for the data in each table.

One of the other possibilities is to combine the data from all the columns into one big row (you can choose some character for the values ​​of the separete column), then you should be able to use the union all clause to combine the results from each query - but then you have to parse every line .. And the data types will be lost.