Joining multiple unrelated tables in MySQL

Question

I have a site that stores <select> options in a number of tables, and extracts all of the relevant ones depending on the individual page. At the moment, I wind up with a query like this: SELECT foo FROM foo_tbl;SELECT bar FROM bar_tbl;etc. It's not really a bad problem, but I have to iterate over each select result individually.

I'd like to extract all of them into a single grid, then do something like

if $row['foo'] != NULL { add to the foo options }
if $row['bar'] != NULL { add to the bar options }
etc

If I use a query like SELECT DISTINCT f.foo, b.bar FROM foo_tbl AS f, bar_tbl AS b, I wind up with every possible combination of rows (first foo first bar, first foo second bar, second foo first bar, second foo second bar, etc etc).

Is there a way to do a select like that and having only once instance of each element in a column, and filling the rest of the rows in the column with nulls?

Answer 1

have you considered using a union

select foo as "f1" from footable where ..whatever..
union
select bar as "f1" from bartable where ..whatever..

this would give you one result set .. but if the tables are truly not related in any way .. this will still not help you much ..

Answer 2

You could do something like the following: Assuming table foo has columns a,b,c and table bar has columns d,e,f

Select 'isFOO', a, b, c, null, null, null from foo
Union All
Select 'isBAR',null, null, null, d, e, f from bar