Optional mysql order - maybe?

Question

Here is my table setup:

mysql> describe a;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(11)    | NO   | PRI | NULL    | auto_increment | 
| x     | int(11)    | YES  |     | NULL    |                | 
| y     | varchar(1) | YES  |     | NULL    |                | 
+-------+------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> describe b;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(11)    | NO   | PRI | NULL    | auto_increment | 
| a     | int(11)    | NO   |     | NULL    |                | 
| z     | varchar(1) | YES  |     | NULL    |                | 
+-------+------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> select * from a;
+----+------+------+
| id | x    | y    |
+----+------+------+
|  1 |    1 | a    | 
|  2 |    2 | b    | 
|  3 |    3 | c    | 
|  4 |    4 | d    | 
+----+------+------+
4 rows in set (0.01 sec)

mysql> select * from b;
+----+---+------+
| id | a | z    |
+----+---+------+
|  1 | 3 | q    | 
|  2 | 2 | a    | 
|  3 | 1 | u    | 
|  4 | 4 | x    | 
+----+---+------+
4 rows in set (0.01 sec)

Here is what I want to do:

mysql> select a.* from a, b where a.id = b.a order by b.z;
+----+------+------+
| id | x    | y    |
+----+------+------+
|  2 |    2 | b    | 
|  3 |    3 | c    | 
|  1 |    1 | a    | 
|  4 |    4 | d    | 
+----+------+------+
4 rows in set (0.00 sec)

However, I want to use syntax like "SELECT * FROM ORDER BY (SELECT a FROM b ORDER BY z)".

Is it possible?

Answer 1

Sorting by subquery is possible if the Subquery returns exactly 1 result, which means that you need to join the tables inside the subquery.

I am just setting this up in the query builder in my application, something like this should work for you:

SELECT a.*
FROM a,b
WHERE a.id = b.a
ORDER BY (
   SELECT z
   FROM b
   WHERE a.id = b.a
) ASC

Answer 2

I really don’t think it’s possible how you describe it, I think you will need to join the tables.

: http://www.w3schools.com/Sql/sql_join_inner.asp

Answer 3

SELECT * FROM a ORDER BY (SELECT a FROM b ORDER BY z)

, , . -, , ORDER BY . , :

ERROR 1242 (21000): Subquery returns more than 1 row

, a , . , , .

, a ( b ), , , :

SELECT DISTINCT a.* 
FROM a JOIN b ON (a.id = b.a)
ORDER BY b.z;