Selecting columns in UNION

Question

$queryActivities = mysql_query("
SELECT ua.status, ua.date, 'status' AS is_table FROM users_statuslog ua
   WHERE ua.uid = '{$showU[id]}'
UNION ALL 
SELECT us.message, us.date 'wall' FROM users_wall us 
   WHERE us.uid = '{$showU[id]}'
ORDER BY `date` DESC");

This is what I have right now. And I need some more columns from users_wall, more than I need in users_statuslog. How can I select them? Because I can not do like this:

(Lets say I also want isReplyFrom and viewed from the users_wall)

$queryActivities = mysql_query("
SELECT ua.status, ua.date, 'status' AS is_table FROM users_statuslog ua
   WHERE ua.uid = '{$showU[id]}'
UNION ALL 
SELECT us.message, us.date, us.isReplyFrom, us.viewed 'wall' FROM users_wall us 
   WHERE us.uid = '{$showU[id]}'
ORDER BY `date` DESC");

I’m getting:

The used SELECT statements have a different number of columns.

Answer 1

You need to add some fake columns to first query

$queryActivities = mysql_query("
SELECT ua.status, ua.date, null AS isReplyFrom, null AS viewed, 'status' AS is_table FROM users_statuslog ua
   WHERE ua.uid = '{$showU[id]}'
UNION ALL 
SELECT us.message, us.date, us.isReplyFrom, us.viewed, 'wall' FROM users_wall us 
   WHERE us.uid = '{$showU[id]}'
ORDER BY `date` DESC");

Answer 2

The SELECT statements cannot have a different number of columns when using UNION. You may want to add a constant field in place of the extra column. Maybe something like this:

SELECT ua.status, ua.date, 0 AS isReplyFrom, 'status' AS is_table 
FROM   users_statuslog ua
WHERE ua.uid = '{$showU[id]}'
UNION ALL 
SELECT us.message, us.date, us.isReplyFrom, us.viewed 'wall' 
FROM users_wall us 
WHERE us.uid = '{$showU[id]}'
ORDER BY `date` DESC

Answer 3

As the message says, you have two select with a different number of columns.
An union will "concatenate" the result of your first select to your second select, but if the number of columns isn't the same it can't work.

Either you find a way to have the same number of columns or you add dummy columns to your request:

SELECT ua.status, ua.date, 'dummy' AS replyFrom, 'dummy' AS viewed, 'status' AS is_table FROM users_statuslog ua
    WHERE ua.uid = '{$showU[id]}'
UNION ALL
SELECT us.message, us.date, us.isReplyFrom, us.viewed, 'wall' FROM users_wall us 
    WHERE us.uid = '{$showU[id]}'
ORDER BY `date` DESC

---

Resources :

• w3school.com - SQL UNION operator