MySQL Multiple Rows Same Table

Question

I have a MySQL table that holds rows of configuration data, ie:

id  item   value1  value2
2   class   ship    bow
3   class   car     tires
5   reg     ship    level1
7   reg     ship    level2
9   reg     car     level5

I am trying to create a query that selects all rows where item='class', and returns a data set that matches the initial query + all rows where item='reg' and value1=value1 (from initial results).

So in this scenario, the result set should look like:

class  ship  bow   reg   ship  level1
class  ship  bow   reg   ship  level2
class  car   tires reg   car   level5

I am a bit frustrated, and hope this makes sense. Thanks for any pointers in the right direction!

Answer 1

You'll have to join the table to itself. Assuming your table is called configs (because you didn't tell us what it's called), something like this should work:

select t1.item, t1.value1, t1.value2, t2.item, t2.value1, t2.value2
    from configs as t1
    inner join configs as t2
    on t2.value1 = t1.value1 and t2.item = 'reg'
    where t1.item = 'class';

If you require all rows that have no matching reg row returned in the results as well, change inner join to left outer join. Make sure you have indexes on item and value1 if you want this query to perform decently.

Here is a quick proof of concept that shows the query above works:

mysql> create table configs (
    -> id int unsigned primary key auto_increment,
    -> item varchar(32) not null,
    -> value1 varchar(32) not null,
    -> value2 varchar(32) not null,
    -> index (item),
    -> index (value1)
    -> ) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into configs (id, item, value1, value2) values
    -> (2, 'class', 'ship', 'bow'),
    -> (3, 'class', 'car', 'tires'),
    -> (5, 'reg', 'ship', 'level1'),
    -> (7, 'reg', 'ship', 'level2'),
    -> (9, 'reg', 'car', 'level5');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from configs;
+----+-------+--------+--------+
| id | item  | value1 | value2 |
+----+-------+--------+--------+
|  2 | class | ship   | bow    |
|  3 | class | car    | tires  |
|  5 | reg   | ship   | level1 |
|  7 | reg   | ship   | level2 |
|  9 | reg   | car    | level5 |
+----+-------+--------+--------+
5 rows in set (0.00 sec)

mysql> select t1.item, t1.value1, t1.value2, t2.item, t2.value1, t2.value2
    -> from configs as t1
    -> inner join configs as t2
    -> on t2.value1 = t1.value1 and t2.item = 'reg'
    -> where t1.item = 'class';
+-------+--------+--------+------+--------+--------+
| item  | value1 | value2 | item | value1 | value2 |
+-------+--------+--------+------+--------+--------+
| class | ship   | bow    | reg  | ship   | level1 |
| class | ship   | bow    | reg  | ship   | level2 |
| class | car    | tires  | reg  | car    | level5 |
+-------+--------+--------+------+--------+--------+
3 rows in set (0.00 sec)