mysqljoinforeign-keysprimary-keymultiple-join-rows

Multiple join on same table


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

Solution

  • 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