Adding flags to resultset

[cyberRobot]

I'm using UNION to merge two resultsets. Everything works, but I trying to figure out how to add a flag to the resultset.

 

Basically I have a table for pre-defined stakeholders and a table for other stakeholders. Since I'll be using the id from both tables and it may overlap, I would like to create a flag to indicate if its an other stakeholder or not.

 

Here is my query so far:

(SELECT id, org, type FROM stakeholders) 
UNION 
(SELECT id, orgOther, orgOtherType FROM otherStakeholders) 
ORDER BY type, org

 

Here is the "stakeholders" table:

idorgtype

1s1National

2s2State/Local

3s3State/Local

4s4National

 

Here is the "otherStakeholders" table:

idorgOtherorgOtherType

1other_s1National

2other_s2State/Local

 

 

Note that the otherStakeholders table has been simplified...there is a reason why it's stored in a seperate table. 

 

Also, I'm using MySQL 5.0 if it matters.

[cyberRobot]

OK, I figured out a solution:

 

(SELECT id, org, type, CONCAT('0') AS isOther FROM stakeholders)
UNION (SELECT id, orgOther, orgOtherType, CONCAT('1') FROM otherStakeholders)
ORDER BY type, org

 

 

If you know of a better solutions, I'm open to suggestions.

[fenway]

That's pretty much the only way.