CODEWITHSUNDEEP
mysqlunion
delete me
delete me

Reputation:

How can I add a column to this union result?

I have this query (which I removed some keys from for brevity's sake): 

SELECT id as in_id, out_id, recipient, sender, read_flag 
  FROM received WHERE recipient=1
UNION ALL 
SELECT in_id, id AS out_id, recipient, sender, read_flag  
  FROM sent WHERE sender=1

Which combines the results from two tables showing messages sent and received by a given user. What I'd like to do is add a column/flag to the result to distinguish which table the row belongs to so when I display them I can show a relevant icon for sent or received messages. How would I add this?

Upvotes: 17

Views: 40647

Answers (3)

paxdiablo
paxdiablo

Reputation: 881283

This will do it:

SELECT 'r' as type, id as in_id, out_id, recipient, sender, read_flag 
  FROM received WHERE recipient=1
UNION ALL 
SELECT 's' as type, in_id, id AS out_id, recipient, sender, read_flag  
  FROM sent WHERE sender=1

Upvotes: 3

stephenr
stephenr

Reputation: 1173

Just add the column in each select with a hard coded value:

SELECT id as in_id, out_id, recipient, sender, read_flag, 'received' as source_table 
  FROM received WHERE recipient=1
UNION ALL 
SELECT in_id, id AS out_id, recipient, sender, read_flag, 'sent' as source_table 
  FROM sent WHERE sender=1

Upvotes: 4

Mark Byers
Mark Byers

Reputation: 838096

Just add a constant column to each query. It doesn't matter what the type is as long as it's the same in both parts. So you could use 0 and 1 or two strings, for example:

SELECT id as in_id, out_id, recipient, sender, read_flag , 'received' as source
  FROM received WHERE recipient=1
UNION ALL 
SELECT in_id, id AS out_id, recipient, sender, read_flag , 'sent' as source 
  FROM sent WHERE sender=1

Upvotes: 24

Related Questions