MySQL: Use column data from other row in output row in same table

Question

I have a table with categories and subcategories, linked together with column 'cat_parent' which has the parent category id in it.

When I search for something I would like the result to include its parent's category slug (or NULL if it has no parent) as a virtual column 'parent_slug'.

This is my table:

+-----+------------+------------+------------+
| id  | cat_name   | cat_slug   | cat_parent |
+-----+------------+------------+------------+
| 1   | Cars       | cars       | 0          |
+-----+------------+------------+------------+
| 2   | Planes     | planes     | 0          |
+-----+------------+------------+------------+
| 3   | Volvo      | volvo      | 1          |
+-----+------------+------------+------------+
| 4   | Alfa Romeo | alfa-romeo | 1          | 
+-----+------------+------------+------------+
| 5   | Boeing     | boeing     | 2          | 
+-----+------------+------------+------------+
| 6   | Mitsubishi | mitsubishi | 1          | 
+-----+------------+------------+------------+
| 7   | Mitsubishi | mitsubishi | 2          | 
+-----+------------+------------+------------+

When I search for 'volvo' I would like the result to be like this:

+-----+----------+----------+------------+-------------+
| id  | cat_name | cat_slug | cat_parent | parent_slug |
+-----+----------+----------+------------+-------------+
| 3   | Volvo    | volvo    | 1          | cars        |
+-----+----------+----------+------------+-------------+

Or search for mitsubishi, and it would look like this:

+-----+------------+------------+------------+-------------+
| id  | cat_name   | cat_slug   | cat_parent | parent_slug |
+-----+------------+------------+------------+-------------+
| 6   | Mitsubishi | mitsubishi | 1          | cars        |
+-----+------------+------------+------------+-------------+
| 7   | Mitsubishi | mitsubishi | 2          | planes      |
+-----+------------+------------+------------+-------------+

And, imagine I'd do a search for 's' (LIKE '%s%'), it would look like this:

+-----+------------+------------+------------+-------------+
| id  | cat_name   | cat_slug   | cat_parent | parent_slug |
+-----+------------+------------+------------+-------------+
| 1   | Cars       | cars       | 0          | NULL        |
+-----+------------+------------+------------+-------------+
| 2   | Planes     | planes     | 0          | NULL        |
+-----+------------+------------+------------+-------------+
| 6   | Mitsubishi | mitsubishi | 1          | cars        |
+-----+------------+------------+------------+-------------+
| 7   | Mitsubishi | mitsubishi | 2          | planes      |
+-----+------------+------------+------------+-------------+

I hope that makes sense. I wouldn't want to change the table structure or add relational tables, as it works really nice and quick for simple categories.

And, yes, Mitsubishi does build planes. :P

Thanks in advance!

Answer 1

JOIN combines data from multiple tables (or rows in this case) into one result.

In this case, we want all the data + the parent slug.

LEFT JOIN combines ALL the data from the left with what you specify from the right (p.cat_slug as parent_slug), cat_parent in this case, where c.cat_parent from the left matches p.id on the right (on c.cat_parent = p.id) and get the cat_slug and output as a new column 'parent_slug' (p.cat_slug as parent_slug).

SELECT c.*, p.cat_slug as parent_slug FROM YOUR_TABLE_NAME as c
Left Join YOUR_TABLE_NAME as p on c.cat_parent = p.id;

Replace YOUR_TABLE_NAME with the correct table name.