select * from two tables with different # of columns

Question

How would I select different columns from two different tables, such as:

SELECT username, email FROM `table1` 
UNION
 SELECT * FROM `table2` WHERE username = 'user1';

I'm getting an error "#1222 - The used SELECT statements have a different number of columns". From what I understand UNION will not work,

Is there a way to accomplish this, since I would need unequal number of columns and rows and there are no mutual/similar entries in the two tables (i.e. user1 is not listed in table1)?

Can this not be done in one query?

thank you!

But

Typically this would be handled by two separate select queries... – Amberjack

Is there no way to do this in one query so I would have all the variables at once? – But

It is possible but it's much easier to make two queries then try a kludge to combine the results into a single query. It will also be much harder to read the results properly. – Amberjack

Answer 1

You can fake the missing columns using an alias - e.g.

 SELECT username, email, '' as name FROM `table1` 
 UNION
 SELECT username, email, name FROM `table2` 
 WHERE username = 'user1';

where name is in table2, but not in table1

Unless you're confusing UNIONS with JOINS:

SELECT table1.*, table2.* FROM
table1 INNER JOIN table2
ON table1.username = table2.username

this would merge both tables, so you get all the columns on the same row.

Vegetarian

This is the same answer I would have said. However, it still seems very pointless to do this and less maintainable in the long run. – Amberjack

cool, if this is the consensus (which I believe it is) then I agree I would have an easier time with separate queries. Thanks for your help! – But

I used the above to merge two tables where only the ID column was named differently. Worked perfectly allowing me to merge events and training into one list but link to different views. – Ecumenical

Answer 2

If there's no mutual or similar entries in the two tables, these should be two different select statements.

SELECT username, email FROM `table1`;


SELECT * FROM `table2` WHERE username = 'user1';

What's your motivation for doing otherwise?

Are the entries in table2 related to table1? Would a join be more appropriate?

SELECT t1.username, t1.email, t2.*
FROM table1 t1
    JOIN table2 t2 ON t1.username = t2.username
WHERE t1.username = 'user1';

Lowercase

I'll probably end up doing the separate queries, since there are no related entries. I was just interested in the possibility. Thanks for your insight! – But

Answer 3

In the table with less columns, try

SELECT *, 0 as col1, 0 as col2, ...

etc in order to make them the same number of columns.

Xenophanes