Combine data in two columns into one for sorting

Question

I am trying to JOIN two columns in SELECT and an alias to the third.

I also need to get the data matching the WHERE clause, then sort by column with an alias (MLS_SORT).

This and its options that I tried do not work.

SELECT * FROM (SELECT MLS_AGENT_ID AS MLS_SORT FROM mlsdata) UNION (SELECT MLS_OFFICE_ID AS MLS_SORT FROM mlsdata) WHERE (MLS_AGENT_ID = $agent_narid) OR (MLS_OFFICE_ID = $office_narid) ORDER BY MLS_SORT 

This part works and creates an MLS_SORT alias with the correct values, but I cannot figure out how to limit the results to the WHERE clause above:

 (SELECT MLS_AGENT_ID AS MLS_SORT FROM mlsdata) UNION (SELECT MLS_OFFICE_ID AS MLS_SORT FROM mlsdata) 

Am I at least following the right path, or is this not the right way to continue?

Thanks for any help.

Answer 1

The trick is understanding UNION syntax: UNION query

I think you want:

 SELECT MLS_SORT FROM ( SELECT MLS_AGENT_ID AS MLS_SORT FROM mlsdata WHERE MLS_AGENT_ID = $agent_narid UNION SELECT MLS_OFFICE_ID AS MLS_SORT FROM mlsdata WHERE MLS_OFFICE_ID = $office_narid ) ORDER BY MLS_SORT 

To get two subsets of IDs in one result set, sort them.

But, all this query looks like it will give a two-line result set - one line for the agent and another for the office. Is this what you want?

Your logic efficiently assigns the agent ID and office ID numbers to a single result set. Does this make sense in your application?

Answer 2

try something like this:

  select * from ( (SELECT MLS_AGENT_ID AS MLS_SORT FROM mlsdata WHERE (MLS_AGENT_ID = $agent_narid) ) UNION (SELECT MLS_OFFICE_ID AS MLS_SORT FROM mlsdata WHERE (MLS_OFFICE_ID = $office_narid) )) a ORDER BY MLS_SORT 

change

alternative order

 ORDER BY 1