Fetching row data as column names and set as boolean if exists or not

Question

I have access to a database similar to this:

users:

id  | name
====|========
1   | Tom
2   | Dick
3   | Harry
4   | Sally

exlusions:

user_id | exclusion
========|==============
1       | French only
3       | No magazine
4       | English only
1       | No magazine

Is it possible to query the database to get a result like this?

$users = [
    [
        'id'           => 1,
        'name'         => 'Tom',
        'english_only' => false, // unset or null would be okay
        'french_only'  => true,
        'no_magazine'  => true,
    ],
    // . . .
];

I've been playing around with things like GROUP_CONCAT, PIVOT, and other examples and can't figure out how or if any of it applies.

SQL Fiddle -- thought I could modify this to match my scenario. I didn't get all that far, and as you can see I have no idea what I am doing...

score 2 · Accepted Answer · answered Jun 11 '15 at 19:20

You can use IF in your select to make your 3 columns out of the 1 based on their values.

SELECT id, name,
IF(exclusion='English only',true,false) as english_only
IF(exclusion='French only',true,false) as french_only
IF(exclusion='No magazine',true,false) as no_magazine
FROM users, exclusions
WHERE users.id=exclusions.user_id

score 0 · Answer 2 · edited May 23 '17 at 12:30

I started with @RightClick's answer, but had to tweak it a bit for my SQL server.

SELECT User.id, User.name,
    CASE WHEN Ex.exclusion = 'English Only' THEN 1 ELSE 0 END as english_only,
    CASE WHEN Ex.exclusion = 'French Only' THEN 1 ELSE 0 END as french_only,
    CASE WHEN Ex.exclusion = 'No magazine' THEN 1 ELSE 0 END as no_magazine
FROM users as User
LEFT JOIN exclusions Ex on User.id = Ex.user_id;

So much simpler than what I thought it was going to be after googling and searching SO all day...

Fetching row data as column names and set as boolean if exists or not

2 Answers2