CODEWITHSUNDEEP
mysqljoin
Don Rhummy
Don Rhummy

Reputation: 25830

How avoid joining multiple times to the same table to get results back in single rows?

I have a schema that requires joining to the same table multiple times to get more information on the data pointed to by the columns. Below is an example schema that shows this situation:

SQL Fiddle: http://sqlfiddle.com/#!9/7a4019/1

CREATE TABLE STATE
(
employee INT NOT NULL,
boss INT,
manager INT,
rep INT
);

CREATE TABLE EMPLOYEE
(
id INT NOT NULL,
name VARCHAR(255) NOT NULL
);

INSERT INTO EMPLOYEE (id, name) VALUES (1, "Joe");
INSERT INTO EMPLOYEE (id, name) VALUES (2, "John");
INSERT INTO EMPLOYEE (id, name) VALUES (3, "Jack");
INSERT INTO EMPLOYEE (id, name) VALUES (4, "Jeff");
INSERT INTO EMPLOYEE (id, name) VALUES (5, "Jason");

INSERT INTO STATE (employee, boss, manager, rep) VALUES (1, 2, 3, 4);
INSERT INTO STATE (employee, boss, manager, rep) VALUES (2, 3, 3, 4);
INSERT INTO STATE (employee, boss, manager, rep) VALUES (3, NULL, NULL, 4);
INSERT INTO STATE (employee, boss, manager, rep) VALUES (4, 3, 3, NULL);
INSERT INTO STATE (employee, boss, manager, rep) VALUES (5, 2, 3, 4);

Currently, the only way i know to get this information in single rows for each employee, is left joining multiple times like this:

SELECT employee, b.name AS boss, m.name AS manager, r.name AS rep
FROM STATE
LEFT JOIN EMPLOYEE b ON b.employee = STATE.boss
LEFT JOIN EMPLOYEE m ON m.employee = STATE.manager
LEFT JOIN EMPLOYEE r ON r.employee = STATE.rep

Is there a way to do it without joins and without subqueries?

Upvotes: 1

Views: 1188

Answers (2)

Andrew D. Arenson
Andrew D. Arenson

Reputation: 41

You can avoid doing multiple joins by using aggregate functions, which I recently found useful after hitting the limit (61) of the number of joins that can be done in a query in MySQL/MariaDB.

SELECT s.employee,
       GROUP_CONCAT(if(e.id=s.boss, name, NULL)) as boss,
       GROUP_CONCAT(if(e.id=s.manager, name, NULL)) as manager,
       GROUP_CONCAT(if(e.id=s.rep, name, NULL)) as rep,
FROM STATE s, EMPLOYEE e
GROUP BY s.employee

The above example uses MySQL's GROUP_CONCAT function. It appears not to be an ANSI standard. Other relational databases may have similar functions. A cursory web search turned up a page that discussed aggregate functions for various relational databases: http://www.postgresonline.com/journal/archives/191-String-Aggregation-in-PostgreSQL,-SQL-Server,-and-MySQL.html

Upvotes: 1

O. Jones
O. Jones

Reputation: 108706

You asked:

Is there a way to do it without joins and without subqueries?

Not really. You are using the JOIN operations precisely as they're intended to be used -- each JOIN reflects a specific relationship between rows of a table.

Upvotes: 2

Related Questions