SQL : Cartesian product + Join + Flatten

Question

Under MYSQL 5.6, I have the following 2 tables :

CREATE TABLE User
(
    id        INTEGER,
    login     VARCHAR(32),
    isservice BOOLEAN
);

CREATE TABLE Certificate
(
    userid        INTEGER,
    serviceid     INTEGER,
    certificate   VARCHAR(32)
);

With the following data :

User:

id  |  login   |  isservice
---------------------------
1   | john     | false
2   | bob      | false
3   | serviceA | true
4   | serviceB | true

Certificate: (example Data, real data in Certificate column are unknown)

userid | serviceid | Certificale
---------------------------
1      | 3         | Alpha
1      | 4         | Bravo

I createdthe following SQLFiddle : http://sqlfiddle.com/#!9/719a8/2

I'm searching for the request (not stored procedure) that would give me the following :

id | login | certif_serviceA | certif_serviceB
-----------------------------------------------
1  | john  | Alpha           | Bravo
2  | bob   | NULL            | NULL

So far, I have tried basing my request on :

SELECT  user.id, user.login,
        service.login as serviceName
FROM User, User as service WHERE service.isservice=1 AND user.isservice=0

But i'm stuck there :s

you should better redesign your DB schema. to keep `Users` in the same table as `Services` is **very bad** idea — Alex, Oct 21 '15 at 15:11
@Alex : I think you are right, however in my real case it makes a bit more sense than in the example I provided (services are users because in my app they are very very close). And saddly I can't modify the schema. — IggY, Oct 21 '15 at 15:14

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

As I already commented you definitely should redesign your DB.

What you are asking for is pivot table which is not so easy to achieve using mysql. You can read here

But I have this approach for you:

http://sqlfiddle.com/#!9/4dc0b2/1

SELECT  user.id, user.login,
    GROUP_CONCAT(Services.login) as ServicesName,
    GROUP_CONCAT(certificate.certificate) as Certificates
FROM User
LEFT JOIN Certificate
ON Certificate.userid = User.id
LEFT JOIN User Services
ON Certificate.serviceid = Services.id
WHERE NOT User.isservice
GROUP BY User.id

or

SELECT  user.id, user.login,
    GROUP_CONCAT(CONCAT(Services.login,':',certificate.certificate)) as ServicesName
FROM User
LEFT JOIN Certificate
ON Certificate.userid = User.id
LEFT JOIN User Services
ON Certificate.serviceid = Services.id
WHERE NOT User.isservice
GROUP BY User.id

Probably you can accept it for current case.

UPDATE

SELECT  user.id, user.login,
    GROUP_CONCAT(Services.login) as ServicesName,
    GROUP_CONCAT(certificate.certificate) as Certificates
FROM User
LEFT JOIN User Services
ON Services.isservice
LEFT JOIN Certificate
ON Certificate.userid = User.id
  AND Certificate.serviceid = Services.id
WHERE NOT User.isservice
GROUP BY User.id

http://sqlfiddle.com/#!9/1c062/4

There is a problem if not all certificates are defined for a User : I get : (1,john,serviceB,Bravo) instead of (1,john,(serviceA,serviceB),(Null,Bravo)) — IggY, Oct 21 '15 at 15:34
google pivot table if you really need that. in 99% cases there is no need. in your case you should redesign your DB. but ok, I'll set another query for you even if it is weird. give mi a minute. — Alex, Oct 21 '15 at 15:44

score 1 · Answer 2 · answered Oct 21 '15 at 16:10

1

This works:

SELECT id,
       login,
       MIN(Certificate.certificate) AS certif_serviceA,
       MAX(Certificate.certificate) AS certif_serviceB
FROM USER
LEFT JOIN Certificate ON USER.id = Certificate.userid
WHERE isservice = 0
GROUP BY USER.id

answered Oct 21 '15 at 16:10

Anthony

36,459
25
97
163

SQL : Cartesian product + Join + Flatten

2 Answers2