MySQL left join twice on table with alias/prefix

user_2602109 0
Viewed 55

So I have the following tables:

bans

id offender_steamid admin_steamid reason
1 0000001 000002 Some text

mybb57_users

id loginname
1 0000001
2 0000002

The I'm basically trying to get the mybb57_users record for offender_steamid & admin_steamid in the bans record, I have this query:

SELECT * FROM bans
LEFT JOIN mybb57_users AS uOffender ON uOffender.loginname = bans.offender_steamid
LEFT JOIN mybb57_users AS uAdmin ON uAdmin.loginname = bans.admin_steamid
ORDER BY bans.id DESC LIMIT 10

Now this works OK in phpmyadmin - however, when running through with PHP, it's creating duplicate indexes on the result - I'm using aliases here, I would've thought the results would be prefixed or something - how can I prefix the tables so I can distinguish between the two records?

mysql
1 Answers

Don't use * but list the columns (you need) and alias them.

SELECT bans.id AS bans_id,
       ...
       uoffender.id AS uoffender_id,
       ...
       uadmin.id AS uadmin_id,
       ...
       FROM bans
            LEFT JOIN mybb57_users AS uoffender
                      ON uOffender.loginname = bans.offender_steamid
            LEFT JOIN mybb57_users AS uadmin
                      ON uAdmin.loginname = bans.admin_steamid
       ORDER BY bans.id DESC
       LIMIT 10;
user_96614240
Related