0

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!

Improve this question
7
  • Your examples all show the parent's cat name (but in a column labeled parent_slug), not the parent's cat slug as your text indicates; which is correct? Commented Jul 12, 2024 at 5:11
  • 1
    This is a simple self left join Commented Jul 12, 2024 at 5:12
  • Updated some cat_name values so it's easier to differentiate and fixed a typo in cat_slug that was 'volvo' instead of 'cars'. I tried left join, but I just don't understand how they work and kept getting ambiguous errors and eventually gave up. Commented Jul 12, 2024 at 6:11
  • Does cat_slug include models? If not it seems redundant. Commented Jul 12, 2024 at 6:20
  • 1
    To add a bit more substance to your question, you could edit it and add your best attempt at a left join along with the errors you got. A possible answer could then explain the errors to you, or what's wrong with the query. That way you learn a lot more, and your question would be more informative to others. We all learn from our mistakes, not from copying ready made answers. Commented Jul 12, 2024 at 6:20

1 Answer 1

Reset to default
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.

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

3 Comments

Not too sure why your answer has been downvoted, as it works a charm! Thank you so much! Now spend some time figuring out how it works, as I have never successfully used left joins, all they got me were syntax and column count errors haha.
@Paul: It was probably downvoted because it left you wondering why it works. Will you really try to work out why it works, or simply copy it and move on? If you do work it out, you are encouraged to edit this answer to improve it. You have quite a low reputation, so I'm not sure you can edit it, but this answer would be greatly improved if it had an explanation.
@KIKOSoftware Spent some time fiddling with the query and watching some tutorials too - now I know not just LEFT JOIN, but also INNER, RIGHT and FULL JOIN. I have edited the answer. Mind you I am not that great in explaining how stuff works, but I gave it my best try. :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.