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!
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.