How to do a UNION ALL SELECT MySQL query with various columns but order by one specific column?

Question

One problem is that they have different named columns

So for example

Table 1
- id
- value_a
- value_b

Table 2
- sid
- value_c
- value_d

I need something like

SELECT id,value_a,value_b AS results FROM table_1 UNION ALL SELECT sid,value_c,value_d AS results FROM table_2 ORDER BY value_a/value_c LIMIT 30

I realize the value_a/value_c isn't what I'd actually do but that's what I mean.

Right now I am able to get the different columns and order alphabetically, I'm just trying to avoid querying again to first figure out which table the current row came from and then grabbing the rest, right now what I've got is

SELECT value_a AS name FROM table_1 UNION ALL SELECT value_c AS name FROM table_2 ORDER BY name LIMIT 30

Can I do what I'm trying to do? I've been going through a bunch of stack overflow threads, not really getting it to work.

edit: this might be what I want

Using union and order by clause in mysql

edit:

The answer I came to from posts here and from other stackoverflow posts:

So you've got your two tables.

To select from both and order by a specific column and also echo out each specific column:

SELECT id AS id, value_a AS value_1, value_b AS value_2, 1 as tblname FROM Table_1 UNION ALL SELECT sid as id, value_c as value_1, value_d as value_2, 2 as tblname FROM Table_2 ORDER BY value_1 LIMIT 30

Then you can write a catch like this:

if ($row['tblname'] === "1") {
  // echo out / do stuff to this specific set of columns
  echo $row['value_1']; // echos out value_a
}
else if ($row['tblname'] === "2") {
  // echo out / do stuff to this specific set of columns
  echo $row['value_1']; // echos out value_c
}

Not saying this is the best route, but this single query that gets 30 rows is better than the same command but using more than one query becoming 31 queries (1 query every time you want to get the data knowing what you're looking for in what table)

Please mention about your problem briefly. i can not catch what you want to do? — K.Suthagar, Dec 15 '16 at 05:18
@K.Suthagar I'm trying to get rows from two different tables, each table has unique columns, but I want to order the results alphabetically by two columns (one from each table). So say table_1 and table_2 both have a column for names but one column is spelled Name and the other one is spelled name. The other problem is how to get the columns from each table (not just what I used to order with eg. the AS part of the query)... this might not have been that much more helpful. — Jacob David C. Cunningham, Dec 15 '16 at 05:25
See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Dec 15 '16 at 07:53

score 2 · Answer 1 · answered Dec 15 '16 at 05:17

2

Something like this?

SELECT
    t.id
    t.results
from
    (
        SELECT 
            id
            ,value_a
            ,value_b AS results 
        FROM table_1 
        UNION ALL 
        SELECT 
            sid
            ,value_c
            ,value_d AS results 
        FROM table_2 
    ) as t
ORDER BY results 
LIMIT 30

answered Dec 15 '16 at 05:17

leeyuiwah

6,562
8
41
71

I saw an answer just like this from another thread (linked it above), I will try it out. I should mention that this is actually a MariaDB but apparently MySQL is 99% match with MariaDB... not sure if this will be a problem. – Jacob David C. Cunningham Dec 15 '16 at 05:30

DhanushD · Accepted Answer · 2016-12-16T06:04:33.580

1

Not exactly sure about your requirements. But I assume you need to order the columns alphabetically after the union all and still be able to tell which table the row is actually from.

Correct me if I am wrong.

I would go with a query like this:

SELECT value_a AS name, 1 as tblname FROM table_1 
UNION ALL 
SELECT value_c AS name, 2 as tblname FROM table_2 
ORDER BY name

This will display the table no accordingly.

To allow value_b from table_1 and value_d from table_2 to be displayed in separate columns:

SELECT value_a AS name, value_b, null as value_d, 1 as tblname FROM table_1 
UNION ALL 
SELECT value_c AS name, null as value_b, value_d, 2 as tblname FROM table_2 
ORDER BY name

edited Dec 16 '16 at 06:04

answered Dec 15 '16 at 05:26

DhanushD

36
5

That is what I'm trying to go for but also conserve queries by selecting more than one column per SELECT, so in your example you only selected one column per side eg. value_a, value_c I have that already. I need to keep that same thing and order alphabetically, but also select other columns. I'm not sure if I can do that. It seems like I can. – Jacob David C. Cunningham Dec 15 '16 at 05:32
Just keep on adding columns to the query. The order by will still function on the specified column only. If you could give a sample of your table structures and describe a bit more it would be helpful. – DhanushD Dec 15 '16 at 05:40
Well I did get this to work so thanks for that. I get the table which the current row came from, now my concern is how to grab different columns from each table and output those, not just the "AS name" part. If I added value_b, after value_a, and value_d after value_c, how would I get those two new values, not just value_a and value_c? Anyway thanks for helping me get this far. – Jacob David C. Cunningham Dec 15 '16 at 20:12
This pattern seems to work but I get a warning of "unitialized string offset" if I treat it as an array like $row['name']['value_b'] or $row['name']['value_c'] I actually get those column row values, but there's the warning. – Jacob David C. Cunningham Dec 15 '16 at 20:16
Actually it seems to return whatever column was selected last. – Jacob David C. Cunningham Dec 15 '16 at 20:20
I guess my main concern is are multiple mysql queries the same as multiple HTTP requests, so if I don't get all the data in the main UNION select all loop, and I have to re-query every result, a call of 30 rows would become 60, is what I'm trying to avoid but maybe I don't understand how MySQL calls work. – Jacob David C. Cunningham Dec 15 '16 at 20:21
I assume you are intending to use the result-set returned here on a web application using PHP or some other scripting language. For Mysql once the query has been executed the work is done. It will return a ResultSet which will be used by you on the application end. – DhanushD Dec 16 '16 at 05:57
That is while using the result set no further request will be fired to the MySQL, so you need not worry about the multiple re-query being carried out. Just get all the data you require into the resultset and go ahead with the processing on PHP. – DhanushD Dec 16 '16 at 06:00
Edited the answer to display value_b from table_1 and value_d from table_2 into 2 different columns. – DhanushD Dec 16 '16 at 06:05

How to do a UNION ALL SELECT MySQL query with various columns but order by one specific column?

2 Answers2