Skip to content
Advertisement

SQL – Ordering table with subquery for select

I am trying to do ordering on query with subquery for a name from uuid. Let’s have those two tables in MySQL:

bans:

id uuid time reason
1 c6b8eade-7db9-345b-b838-8f8361552cf5 1642369382 Test
2 0138c279-c5fa-3acd-adaa-8edb7b4569d8 1642384565 Spam
3 3c01262c-a3c3-3133-ba43-92a9ded01c27 1631876477 Hax

users:

id uuid name
45 c6b8eade-7db9-345b-b838-8f8361552cf5 John
100 0138c279-c5fa-3acd-adaa-8edb7b4569d8 Mike
35 3c01262c-a3c3-3133-ba43-92a9ded01c27 Norman

With this query, I can select whole ban record + fetch user’s name from users table:

SELECT
    bans.*,
    (SELECT users.name FROM users WHERE users.uuid = bans.uuid) as name
FROM bans
ORDER BY :column;

But I cannot order by name. How to change this query so I can do ordering by users.name? I tried to wrap my SQL select into another select, but it does not work and I am unable to resolve it 😀

Advertisement

Answer

That is a JOIN query if I ever saw one.

You cannot pass a table or column name as a parameter in either PDO or MYSQLI. The database cannot compile and optimise code with unknown columns or tables in it

SELECT b.*, u.name
FROM bans b
    LEFT JOIN users u ON u.uuid = b.uuid
ORDER BY u.name;
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement