CODEWITHSUNDEEP
phpmysqljoin
costal martignier
costal martignier

Reputation: 35

MYSQL: join multiple tables - replace multiple userID's with user names

I have two tables, one with all the user data and one with some orders. the order is always for one user, but made from another user (user can't order for his self)

in the order table the editor and the user are only stored with their id corresponding to the user table.

here the shortened tables:

`orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `editor_id` int(10) NOT NULL,
  `reason` char(200) COLLATE utf8_unicode_ci NOT NULL,
  `amount` decimal(10,2) NOT NULL
)

`users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(250) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `password` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `email` varchar(250) COLLATE utf8_unicode_ci NOT NULL DEFAULT ''
)

i want to query one of the orders, and get the user_id replaced with user_name and editor_id also replaced with the correct name from the users table. this is what a normal orders query result looks like:

[0] => Array
        (
            [id] => 714
            [user_id] => 97
            [editor_id] => 45
            [reason] => Ausgaben Regale 28.09.13
            [amount] => 150.00
        )

and this is what i want:

[0] => Array
        (
            [id] => 714
            [user_id] => 97
            [editor_id] => 45
            [user_name] => the user name
            [editor_name] => the editor name
            [reason] => Ausgaben Regale 28.09.13
            [amount] => 150.00
        )

I tried so many different joins, but all without success, would love to get the hint pointing me to the right direction.

Upvotes: 2

Views: 1228

Answers (2)

Nabin Kunwar
Nabin Kunwar

Reputation: 1996

SELECT orders.*,user.name as user_name,editor.name as editor_name FROM orders 
inner join users user on orders.user_id=user.id 
inner join users editor on orders.editor_id=editor.id where orders.id="requested id"

Upvotes: 0

Lucas Moeskops
Lucas Moeskops

Reputation: 5443

This?

SELECT o.id, o.user_id, o.editor_id, u1.name as user_name, u2.name as editor_name,
o.reason, o.amount 
FROM orders o 
INNER JOIN users u1 ON o.user_id = u1.id 
INNER JOIN users u2 ON o.editor_id = u2.id
WHERE o.id = "requested order id"

Upvotes: 2

Related Questions