0

I'm trying to write an SQL query to work on a raw feed that's been imported to a MySQL database table. The table's rows loosely resemble this example.

| sku        | group_name | description       | size | type   |
|------------|------------|-------------------|------|--------|
| EX-1000-XS | NULL       | Long text         | XS   | single |
| EX-1000-S  | NULL       | Long text         | S    | single |
| EX-1000-M  | NULL       | Long text         | M    | single |
| EX-1000-L  | NULL       | Long text         | L    | single |
| EX-1001-M  | EX-1001    | Another long text | M    | single |
| EX-1001-L  | EX-1001    | Another long text | L    | single |
| EX-1001    | NULL       |                   |      | group  |

I'm trying to get the ones without a group_name, but with the determined group name set, along with a group row. Ignoring stuff that already was in a grouping. So the output I want from the query is like this.

| sku        | group_name | description       | size | type   |
|------------|------------|-------------------|------|--------|
| EX-1000-XS | EX-1000    | Long text         | XS   | single |
| EX-1000-S  | EX-1000    | Long text         | S    | single |
| EX-1000-M  | EX-1000    | Long text         | M    | single |
| EX-1000-L  | EX-1000    | Long text         | L    | single |
| EX-1000    | NULL       |                   |      | group  |

I've figured out some substring function on the sku column, to get a "key" like value,

SELECT
  SUBSTRING_INDEX(
    sku,
    '-',
    LENGTH(sku) - LENGTH(REPLACE(sku, '-', ''))
  )
FROM
  products
WHERE
  group_name IS NULL
  AND type = "single";

No idea how to use that as a grouping key yet,

I set up a fiddle with the example, https://www.db-fiddle.com/f/ua6iwXGHcPDVKbCkqHvs2E/0

  • Can you elaborate what grouping key? – Sam Dec 8 '18 at 4:51
  • @sam, the key a substring of the sku, to be used to identify a group. – J. M. Becker Dec 8 '18 at 4:57
2

db-fiddle has strange effect on columns are not in order as per sql statement but works fine on my local Mysql 8.0

select sku,
       case  WHEN group_name IS NULL
             AND  type='single'
             THEN  TRIM(TRAILING '-' FROM REPLACE(sku,size,''))
              ELSE group_name                  
        END 
        AS group_name,
        description,
        size,
        type
from products

This gives your desired output with cte expressions i.e excluding existing rows plus grouping key(same output as bottom table in your question).Tested on your dbfiddle

WITH cte1
AS
  (
   SELECT sku,
          TRIM(TRAILING '-' FROM REPLACE(sku,size,''))      
          group_name,
          description,
          size,
          type
   FROM   products
   WHERE  group_name IS NULL
   AND    type = 'single'
  )

SELECT * FROM cte1

UNION

SELECT   DISTINCT(group_name) sku,
         NULL group_name,
         '' description,
         '' size,
         'group' type
FROM     cte1 
  • OK, I see the idea you did there, using the specific attribute column data to get the substring. That is an idea that might be better than stripping the right most delimiter, which is what I was doing. It's close-ish, I really can't select the excluded stuff though, it's just a rough example, the real data has many more columns. I think I'm going to need to create a header row, append it to the output, and then do something like your case as well. – J. M. Becker Dec 8 '18 at 5:01
  • 1
    You can use both options step 1 will include all rows and union with grouping key(it needs to be modified a little further with cte expression) or step 2 excluding existing non null values in group_name column along with grouping key. – Sam Dec 8 '18 at 7:02
  • Thank you for your help, I'm gonna use this method. – J. M. Becker Dec 8 '18 at 14:20
0
SELECT * FROM products WHERE group_name IS NOT NULL
UNION ALL
SELECT * FROM products WHERE group_name IS NULL AND type = 'group';
  • You got the part I didn't want, appreciate the effort though. In other words, the output should look like the lower table example. – J. M. Becker Dec 8 '18 at 3:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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