Selecting columns in UNION
$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 differ开发者_高级运维ent number of columns.
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
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");
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
精彩评论