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!

@Toby Allen: True, but in this case the SQL is pretty straight forward. — Asaph
– Asaph, Commented Feb 26, 2012 at 20:41

Asaph · Accepted Answer · 2012-02-26 23:01:48Z

10

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)

edited Feb 26, 2012 at 23:01

answered Feb 26, 2012 at 20:40

Asaph

163k25 gold badges204 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

James Black Over a year ago

I think you have an error, as you are comparing t2.value1=t1.value1, as I think the on clause should be value1=item.

Asaph Over a year ago

@JamesBlack: No. I got it right. Read the question again and look at his sample data. The item column only ever contains the values class and reg. The value1 column is what he wants to match on.

Jimmyb Over a year ago

Nicely detailed. I was able to test and implement in a very short time. Thanks!

Michał Turecki · Accepted Answer · 2012-02-26 20:39:44Z

0

SELECT
  t1.*,
  t2.*
FROM
  table t1
  LEFT JOIN table t2 ON t2.item = 'reg' AND t2.value1 = t1.value1
WHERE
  t1.item = 'class'

answered Feb 26, 2012 at 20:39

Michał Turecki

3,2074 gold badges40 silver badges53 bronze badges

4 Comments

Asaph Over a year ago

The select columns should be changed to match what was asked for in the question. Also, table is a SQL reserved word so you should surround it with backticks or change it to something else.

Michał Turecki Over a year ago

Answer was posted before the question was edited, I won't change it for clarity of an answer.

Asaph Over a year ago

The only edits on the question were formatting. No content edits happened. You can view the revision history.

Michał Turecki Over a year ago

Agreed, before formatting example result set was the same and I haven't noticed that, yet to illustrate a way of selecting original result set plus additional result set from the same table, the answer is adequate.

Collectives™ on Stack Overflow

MySQL Multiple Rows Same Table

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related