CODEWITHSUNDEEP
mysqlsqlunion
Jayapal Chandran
Jayapal Chandran

Reputation: 11140

union two tables and order by a common field like name which is present in both the tables

I have two tables a and b which has a field name in it.

I need to list the data from these two tables. I thought of using union but in the result list data from the first table appears and then followed by the second.

what i want is to order by the field name so the result should be a mixed up of two tables in the order of name that is order by name.

Upvotes: 2

Views: 4690

Answers (3)

Jayapal Chandran
Jayapal Chandran

Reputation: 11140

select slug, name, 1 as mt 
  from tablea 
union 
select slug, name, 0 as mt 
  from tableb 
 order 
    by name;

The above is working well for me. will there be any complications in the result of this?

Upvotes: 4

Elq
Elq

Reputation: 151

Or, you could use a Join query such as:

SELECT tablea.firstname, tablea.middlename, tablea.lastname, tableb.phone 
FROM tablea, tableb 
WHERE tablea.ID = tableb.ID

Then, you could sort the result however you like.

Upvotes: 0

Harish
Harish

Reputation: 2324

Suppose your query is

SELECT field1 FROM TABLE1 WHERE 1 
UNION SELECT field1 FROM TABLE2 WHERE 1

u can make it a subquery like this

 SELECT * FROM (SELECT field1 FROM TABLE1 WHERE 1 
UNION SELECT field1 FROM TABLE2 WHERE 1) AS `result` ORDER BY `result`.`field1`

Upvotes: 0

Related Questions