Geek asks and answers

Select * from two tables with different columns

How to select different columns from two different tables, for example:

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

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

Is there a way to do this, since I will need an unequal number of columns and rows and , in both tables there are no mutual / similar records (for example, user1 is not listed in table 1)?

Could this not be done in a single request?

Thank you!

+5
source share
3 answers

You can fakemissing columns using an alias - for example,

 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 table 1

If you are not confusing UNIONS with JOINS:

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

, .

+6

, select.

SELECT username, email FROM `table1`;


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

?

table2 table1? join?

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

In a table with fewer columns, try

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

etc. to make them the same number of columns.

+3
source

More articles:


All Articles