CODEWITHSUNDEEP
mysql
charlie
charlie

Reputation: 481

SQL order rows in a table by value in another table

I am running this query to select rows from admin table where display_on_website value is 1 in admin_meta table.

SELECT 
                a.sequence as sequence, 
                CONCAT(a.forename, ' ', a.surname) as name, 
                a.position as position 
            FROM 
                admin a, admin_meta am 
            WHERE 
                a.disabled = '' AND 
                a.sequence = am.user AND 
                am.field = 'display_on_website' AND 
                am.value = '1'

I have a row in admin_meta table for each user that is website_order - how can i order by this value?

Upvotes: 1

Views: 35

Answers (1)

Barmar
Barmar

Reputation: 780724

You need to join with the admin_meta table twice, once to get the display_on_website value, and again to get the website_order value to order by.

SELECT a.sequence, CONCAT(a.forename, ' ', a.surname) AS name, a.position
FROM admin AS a
JOIN admin_meta AS m1 on a.sequence = m1.user
JOIN admin_meta AS m2 ON a.sequence = m2.user
WHERE a.disabled = ''
AND am1.field = 'display_on_website' AND am1.value = '1'
AND am2.field = 'website_order'
ORDER BY CAST(am2.value AS DECIMAL)

Upvotes: 2

Related Questions