CODEWITHSUNDEEP
phpmysqlsql-order-by
Sami El Hilali
Sami El Hilali

Reputation: 1031

How to make an Order By a field but with a condition inside?

Please masters, I have three table, that I need to select data from using a LEFT JOIN; and that I need to order by a field q (that exists in table3) but only where state = 1 (that exists also in table3).

I tried this but it doesn't work : 

SELECT * FROM table 1
         LEFT JOIN table2 ON x = y
         LEFT JOIN table3 ON z=w
         WHERE w = 1
         ORDER BY q IN ( SELECT q FROM table3 WHERE state = 1);

Please any suggestions ?

Upvotes: 1

Views: 102

Answers (3)

berty
berty

Reputation: 2206

Try something like that :

SELECT * FROM table 1
         LEFT JOIN table2 ON x = y
         LEFT JOIN table3 ON z=w
         WHERE w = 1 AND table3.state = 1
         ORDER BY q

Upvotes: 0

shadyyx
shadyyx

Reputation: 16055

Try this:

SELECT *,
    (SELECT q FROM table3 WHERE state = 1) as sort_order
FROM table 1
    LEFT JOIN table2 ON x = y
    LEFT JOIN table3 ON z=w
WHERE w = 1
ORDER BY sort_order

As I do not know what the desired result should be, possibly this is the right query:

SELECT *
FROM table1 as t1
    LEFT JOIN table2 as t2 ON t1.x = t2.y
    LEFT JOIN table3 as t3 ON t2.z = t3.w AND t3.state = 1
WHERE t3.w = 1
ORDER BY t3.q

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24046

How abt this:

SELECT * FROM table 1
         LEFT JOIN table2 ON x = y
         LEFT JOIN table3 ON z=w
         WHERE w = 1
         ORDER BY case when state=1 then 0 else 1 end,q

Upvotes: 3

Related Questions