codegasmer codegasmer - 5 months ago 8
SQL Question

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

Answer

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

Comments