0

I would like to preface this by saying that I'm relatively new to writing SQL. Please bear with my stupidity.

I'm trying to extract data from multiple tables onto a spreadsheet

I have 3 tables that look like so.

Account

id name is_setup
1 Acc1 1

Profile

id account_id(fk) name type
1 1 profname shopify

ProfilePreferences

id profile_id(fk) name value
1 1 is_quote_enabled 1
2 1 auto_sync 1

I have a query that returns what I want but I can't wrap my head around how to get results in the same row rather than them being presented as 2 rows for the settings in the preferences table.

My query looks like so...

SELECT acc.name,
       prof.`integration_type`,
       prof.`name`,
       prof.`status`,
       acc.`is_setup`,
       prof.pref_name,
       prof.`pref_value`,
       sh_acc_id,
       sid
FROM accounts acc
INNER JOIN
    (SELECT prof.account_id AS pid,
            prof.`integration_type`,
            prof.`name`,
            prof.`status`,
            prof_pref.`name` AS pref_name,
            prof_pref.`value` AS pref_value
     FROM profiles prof
     INNER JOIN profile_preferences prof_pref ON prof.`id` = prof_pref.`profile_id`
     AND prof.`integration_type`="shopify"
     WHERE prof_pref.`name`="is_quote_enabled"
         AND prof_pref.`value`= 1
         OR prof_pref.`name`="auto_sync"
         AND prof_pref.`value`= 1
         OR prof_pref.`name`="is_quote_enabled"
         AND prof_pref.`value`= 0
         OR prof_pref.`name`="auto_sync"
         AND prof_pref.`value`= 0) prof ON acc.`id` = pid
WHERE acc.`is_setup` = 1;

The output I get looks like so...

name integration_type name status is_setup pref_name pref_value
Acc1 shopify profname 1 1 auto_sync 1
Acc1 shopify profname 1 1 is_quote_enabled 1

Is there a way to output something like this:

name integration_type name status is_setup auto_sync is_quote_enabled
Acc1 shopify profname 1 1 1 1
6
  • The keyword to search for is "pivot"
    – mustaccio
    Commented Dec 8, 2021 at 20:48
  • @mustaccio can you give me a simple example of how I can use pivot for my use-case? Commented Dec 8, 2021 at 21:33
  • @mustaccio I tried doing the following in my outer most select statement SELECT acc.name, prof.integration_type, prof.name, prof.status, acc.is_setup, CASE when pref_name="auto_sync" THEN pref_value AS is_auto_sync_enabled, prof.pref_value, sh_acc_id, sid FROM accounts acc.... but I get an error saying there is a syntax error, can you point me towards what Im doing wrong here? Commented Dec 8, 2021 at 21:56
  • You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS is_auto_sync_enabled, CASE when prof.pref_name="is_quote_enabled" ' at line 6 this is the error i get Commented Dec 8, 2021 at 21:59
  • Check the CASE expression syntax.
    – mustaccio
    Commented Dec 9, 2021 at 0:02

1 Answer 1

0

Thanks to @mustaccio I was finally able to re-factor the query to output exactly what I wanted

The CASE syntax was incorrect and I was missing the END clause

This is the final query I ran

SELECT acc.`id`,
       acc.`name`,
       acc.`created_at`,
       acc.`is_setup`,
       SUM(CASE prof.pref_name
               WHEN 'auto_sync' THEN prof.`pref_value`
               ELSE 0
           END) AS `auto_sync`,
       SUM(CASE prof.pref_name
               WHEN 'is_quote_enabled' THEN prof.`pref_value`
               ELSE 0
           END) AS `is_quote_enabled`
FROM accounts acc
INNER JOIN
    (SELECT prof.account_id AS profile_account_id,
            prof.`integration_type`,
            prof_pref.`name` AS pref_name,
            prof_pref.`value` AS pref_value
     FROM profiles prof
     INNER JOIN profile_preferences prof_pref ON prof.`id` = prof_pref.`profile_id`
     AND prof.`integration_type`="shopify"
     WHERE prof_pref.`name`="is_quote_enabled"
         AND prof_pref.`value`= 1
         OR prof_pref.`name`="auto_sync"
         AND prof_pref.`value`= 1
         OR prof_pref.`name`="is_quote_enabled"
         AND prof_pref.`value`= 0
         OR prof_pref.`name`="auto_sync"
         AND prof_pref.`value`= 0) prof ON acc.`id` = profile_account_id
WHERE acc.`is_setup` = 1
GROUP BY `acc`.`id`,
         `acc`.`name`,
         `acc`.`created_at`,
         `acc`.`is_setup`;

Now the output produced looks like so...

id name created_at is_setup auto_sync is_quote_enabled
1 name 2021-12-09 1 1 1

Not the answer you're looking for? Browse other questions tagged or ask your own question.