| Marker in UNION ALL
|
Marked
msg:4093107 | 2:18 am on Mar 8, 2010 (gmt 0) |
Hi all,
I'm trying to create a query that retrieves comments from a range of applications. The following query selects comments from profiles and blogs:
SELECT comment_by_member_id as id,comment_content as comment,comment_date as date FROM ibf_profile_comments
WHERE comment_for_member_id=1
UNION ALL
SELECT com.member_name as id,com.comment_text as comment,comment_date as date FROM ibf_blog_comments com
LEFT JOIN ibf_blog_entries ent ON com.entry_id=ent.entry_id WHERE ent.entry_author_id=1
ORDER BY date DESC
The problem is that when I want to display these comments, I also want to state which application each comment is from. So my question is, is there some way of having a marker for each SELECT statement? For example, every row from the blogs has some marker which equals 0. And every row from the profiles have a mark which equals 1.
Does anyone have any idea how i can do this?
Thanks in advance,
Mark.
|
mark_roach
msg:4093214 | 11:32 am on Mar 8, 2010 (gmt 0) |
You can just add a literal to each select.
eg.
SELECT '1' as marker, comment_by_member_id as id,comment_content as comment,comment_date as date FROM ibf_profile_comments
WHERE comment_for_member_id=1
UNION ALL
SELECT '0' as marker, com.member_name as id,com.comment_text as comment,comment_date as date FROM ibf_blog_comments com
LEFT JOIN ibf_blog_entries ent ON com.entry_id=ent.entry_id WHERE ent.entry_author_id=1
ORDER BY date DESC
|
Marked
msg:4096101 | 7:35 am on Mar 12, 2010 (gmt 0) |
Perfect, thank you very much :)
|
|
|
