# 1222 - Used SELECT statements have different number of columns

Question

Why am I getting # 1222 - the used SELECT statements have a different number of columns? I am trying to download wall posts from these users and friends.

SELECT u.id AS pid, b2.id AS id, b2.message AS message, b2.date AS date FROM ( ( SELECT b.id AS id, b.pid AS pid, b.message AS message, b.date AS date FROM wall_posts AS b JOIN Friends AS f ON f.id = b.pid WHERE f.buddy_id = '1' AND f.status = 'b' ORDER BY date DESC LIMIT 0, 10 ) UNION ( SELECT * FROM wall_posts WHERE pid = '1' ORDER BY date DESC LIMIT 0, 10 ) ORDER BY date DESC LIMIT 0, 10 ) AS b2 JOIN Users AS u ON b2.pid = u.id WHERE u.banned='0' AND u.email_activated='1' ORDER BY date DESC LIMIT 0, 10 

The structure of the wall_posts table looks like id date privacy pid uid message

The friends table structure looks like Fid id buddy_id invite_up_date status

pid stands for profile id. I'm not quite sure what is going on.

Answer 1

The first statement in UNION returns four columns:

 SELECT b.id AS id, b.pid AS pid, b.message AS message, b.date AS date FROM wall_posts AS b 

The second returns six because * extends to include all columns from WALL_POSTS :

 SELECT b.id, b.date, b.privacy, b.pid. b.uid message FROM wall_posts AS b 

The UNION and UNION ALL require that:

• The same number of columns exists in all statements in a UNION'd query.
• Data types must match in each item / column

Using:

 FROM ((SELECT b.id AS id, b.pid AS pid, b.message AS message, b.date AS date FROM wall_posts AS b JOIN Friends AS f ON f.id = b.pid WHERE f.buddy_id = '1' AND f.status = 'b' ORDER BY date DESC LIMIT 0, 10) UNION (SELECT id, pid, message, date FROM wall_posts WHERE pid = '1' ORDER BY date DESC LIMIT 0, 10)) 

Answer 2

You take a UNION relationship with four columns ( id , pid , message and date ) with a ratio of 6 columns ( * = 6 columns wall_posts ). SQL does not allow you to do this.

Answer 3

 ( SELECT b.id AS id, b.pid AS pid, b.message AS message, b.date AS date FROM wall_posts AS b JOIN Friends AS f ON f.id = b.pid WHERE f.buddy_id = '1' AND f.status = 'b' ORDER BY date DESC LIMIT 0, 10 ) UNION ( SELECT id, pid , message , date FROM wall_posts WHERE pid = '1' ORDER BY date DESC LIMIT 0, 10 ) 

You selected 4 in the first query and 6 in the second to match them.