SQL list columns that contains '1' by given row

Question

My app reads txt files created by the user, and containing the query text, description, input and output, type of query etc. So I can't just make it easier getting data from the DB and elaborate them in java (which I'm more advanced in).

I have a spare_parts table which is an import from an excel file through CSV import in HEIDI SQL.

It's made this way:

+-------------+---------+---------+---------+---------+---------+
| PART NUMBER | MODEL X | MODEL Y | MODEL Z | MODEL 1 | MODEL 2 |
+-------------+---------+---------+---------+---------+---------+
| PART A      |       0 |       0 |       1 |       0 |       0 |
| PART B      |       1 |       0 |       0 |       1 |       0 |
| PART C      |       1 |       1 |       1 |       0 |       0 |
| PART D      |       0 |       0 |       0 |       1 |       1 |
+-------------+---------+---------+---------+---------+---------+

I need to list the models where a certain part is used, for instance: PART C is used to build model X, Y and Z.

I don't want to list all the columns manually, because they are a lot and because they change often.

What do you suggest?

Answer 1

If I have understood properly, you need a many-to-many relationship, since the number of parts associated to each model is variant. I don't know about this heidisql but the technique must be the same.

Basically, what you have is one table for the parts

parts:

---

id | name

---

another for the models

---

id | name

---

and finally a third table that connects the previous two tables

models_to_parts

---

id | model_id | part_id

---

The relation between these tables is:

And your SQL to retrieve what parts are associated to a model should look like:

SELECT models.name, parts.name FROM models

INNER JOIN models_to_parts ON models.id = models_to_parts.model_id
INNER JOIN parts ON models_to_parts.part_id = parts.id

WHERE models.name = "C" -- or whatever condition you want on model or part 

To construct this many-to-many table you'll need to insert models, then parts, and then read both id's to insert a row that connect them.

Take a look here for further info if you want: http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php

Answer 2

Normalise your schema:

CREATE TABLE normalised_parts (
  PRIMARY KEY (model, part)
) SELECT 'X' AS model, `PART NUMBER` AS part FROM spare_parts WHERE `MODEL X`
UNION ALL
  SELECT 'Y', `PART NUMBER` FROM spare_parts WHERE `MODEL Y`
UNION ALL
  SELECT 'Z', `PART NUMBER` FROM spare_parts WHERE `MODEL Z`
UNION ALL
  SELECT '1', `PART NUMBER` FROM spare_parts WHERE `MODEL 1`
UNION ALL
  SELECT '2', `PART NUMBER` FROM spare_parts WHERE `MODEL 2`
-- etc.

Then your problem reduces to:

SELECT model FROM normalised_parts WHERE part = 'PART C'

If the model columns are unknown/numerous, you can create the above DDL from the information schema and then prepare and execute it:

SELECT CONCAT('
  CREATE TABLE normalised_parts (
    PRIMARY KEY (model, part)
  ) ', GROUP_CONCAT('
    SELECT ', QUOTE(COLUMN_NAME), ' AS model, `PART NUMBER` AS part',
    ' FROM spare_parts WHERE `', REPLACE(COLUMN_NAME, '`', '``'), '`'
    SEPARATOR '
  UNION ALL'))
INTO  @sql
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME   = 'spare_parts'
  AND COLUMN_NAME LIKE 'MODEL %'  -- or whatever is appropriate

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Note that you may need to increase the value of group_concat_max_len, depending on the aggregate length of the column names.