Multiple join on same table

Question

My Schema is

CREATE TABLE IF NOT EXISTS `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
   PRIMARY KEY (`id`)
 ); 

 CREATE TABLE IF NOT EXISTS `reseller_did` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `phone` int(11) NOT NULL,  
  `superadmin_id` int(11) NOT NULL DEFAULT '0',
  `reseller_id` int(11) NOT NULL DEFAULT '0',
  `admin_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  FOREIGN KEY (`superadmin_id`) REFERENCES account(`id`),
  FOREIGN KEY (`reseller_id`) REFERENCES account(`id`),
  FOREIGN KEY (`admin_id`) REFERENCES account(`id`)
)

I want to find the phone with their superadmin, reseller, admin name. The problem is that I cant join multiple times on the same id. The query I have tried

select phone,superadmin_id,reseller_id, admin_id, name from reseller_did join 
account on account.id=reseller_did.admin_id

The above query display the admin name by joining on admin_id but how to get superadmin name and reseller name of the same phone?

EDIT: Sample Input

account table

id     name

3      SuperAdmin1
9      Reseller1
10     Admin1

reseller_did

id  phone        superadmin_id   reseller_id   admin_id

1   9090909090   3                 9             10

Sample output

phone        superadmin    reseller    admin

9090909090   SuperAdmin1  Reseller1   Admin1

Please post some sample input and your expected output based on that. — 1000111, Jun 08 '16 at 07:27
Similar to post [Get multiple values from another table by different relations](http://stackoverflow.com/questions/37674462/get-multiple-values-from-another-table-by-different-relations/37674633#37674633) — Jeeva Balan, Jun 08 '16 at 07:56

score 2 · Accepted Answer · edited May 23 '17 at 10:29

Try this;)

select r.phone, a.name as admin, re.name reseller, s.name as superadmin
from reseller_did r
join account a on a.id = r.admin_id
join account s on s.id = r.superadmin_id
join account re on re.id = r.reseller_id

SqlFiddle Result

Or

SELECT r.phone,
       MAX(CASE WHEN a.id = r.admin_id THEN a.name END) as admin,
       MAX(CASE WHEN a.id = r.reseller_id THEN a.name END) as reseller,
       MAX(CASE WHEN a.id = r.superadmin_id THEN a.name END) as superadmin
FROM reseller_did r
INNER JOIN account a 
ON a.id IN (r.admin_id, r.superadmin_id, r.reseller_id)
GROUP BY r.phone

This sql should thank to @sagi in this question Get multiple values from another table by different relations.

SqlFiddle Result

score 0 · Answer 2 · answered Jun 08 '16 at 07:43

Hope this works

select 
    rd.phone as Phone ,
    a.name as Superadmin,
    a1.name as Reseller,
    account.name as Admin
from
    reseller_did rd
        join
    account ON account.id = rd.admin_id
        join
    account a ON a.id = rd.superadmin_id
        join
    account a1 ON a1.id = rd.reseller_id

1000111 · Answer 3 · 2016-06-08T07:53:07.573

You can make three aliases of account table and INNER JOIN these with reseller_did table

SELECT 
R.id,
R.phone,
A1.name AS superadmin,
A2.name AS reseller,
A3.name AS admin
FROM reseller_did R
INNER JOIN account A1 ON R.superadmin_id = A1.id 
INNER JOIN account A2 ON R.reseller_id = A2.id
INNER JOIN account A3 ON R.admin_id = A3.id ;

SQL FIDDLE DEMO 1

Alternative way:

You can use the following query to get the same result if you don't want to adopt INNER JOIN.

SELECT 
R.phone,
(SELECT name FROM account WHERE id = R.superadmin_id) AS SuperAdmin,
(SELECT name FROM account WHERE id = R.reseller_id) AS Reseller,
(SELECT name FROM account WHERE id = R.admin_id) AS Admin
FROM reseller_did R;

SQL FIDDLE DEMO 2

Caution: You are storing phone number in INT datatype. You shouldn't use INT for storing phone number. Instead you should use VARCHAR.

Multiple join on same table

3 Answers3

SQL FIDDLE DEMO 1

SQL FIDDLE DEMO 2