CODEWITHSUNDEEP
mysqlsql-order-by
Deen
Deen

Reputation: 51

Sort MySQL query ASC or DESC depending on value of other table

I got a query and I want wort sort the result depending on another database.

This is my query:

SELECT    a.`name`
FROM      `article` AS a
LEFT JOIN `container` AS b
       ON b.`id` = a.`container`
WHERE     a.`container` = '1'
ORDER BY  b.`sort` ASC;

This works fine, but it always sorts the result ascending. Bute I want ist to be sorted depending on the value in the container table.

Article table

id | container | name    | gender
---|---------------------|---------
1  | 1         | Michael | male
2  | 1         | Joe     | male
3  | 2         | Karen   | female
4  | 2         | Andrew  | male
5  | 3         | Jessica | female


Container table

id | sort   | order
---|--------|----
 1 | name   | asc
 2 | name   | desc
 3 | gender | asc
 4 | name   | asc
 5 | gender | desc

How can I change my query to get the order from the container table?

I would expect this result:

id | container | name    | gender
---|---------------------|---------
2  | 1         | Joe     | male
1  | 1         | Michael | male

Upvotes: 0

Views: 1320

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562220

You can't do this in one query.

A given SQL query must be fixed at the time you prepare the query, which is before the execution begins. You can't make the behavior change depending on what data it discovers while executing.

You'll have to read the sort direction in one query, and then format a dynamic SQL query with the values you discover.

SELECT `sort`, `order` FROM container WHERE id = 1

Fetch these data into application variables like $sort_column, $direction. Then use them:

$sql = "
    SELECT    a.`name`
    FROM      `article` AS a
    WHERE     a.`container` = '1'
    ORDER BY  {$sort_column} {$direction}";

...prepare $sql as a query...

...execute query...

(I'm assuming PHP syntax for the variables, but you can do something similar in any language.)

Upvotes: 1

JeffUK
JeffUK

Reputation: 4231

Does something like this work? 

ORDER BY CASE WHEN b.`order` = 'ASC' b.`sort` ELSE -b.`sort` END;

If not please provide some sample data and the expected result for that data so we can understand your requirements better.

Upvotes: 0

Related Questions