List ID with name in the same table

Asked

Viewed 126 times

2

I need some help.

I have a table of categories that has the fields:

  • id;
  • name;
  • description;
  • id_categoria_pai;
  • id_user;

I want to get the category name when the parent id is equal to the category id.

Example:

The Car category has as parent the Vehicles category id = 4; name = Car; Description = any of them; id_categoria_pai = 1 (vehicles); id_user = 1;

I need to display the name of the parent category, in this case, Vehicles.

How can I do that? I tried anyway and I can’t.

Or do I create separate tables? I don’t think ne.

Thank you!

php mysql

  • You can use Join, + or - like this: SELECT * FROM categories as cat LEFT JOIN categories_parent as cat_parent ON cat_parent.id = cat.id_categoria_parent; Of course, you need to change for your situation and table names in order...

  • Dude, I’m kinda lost hehe, but this is gonna work on the same table?

  • What do you mean? I didn’t understand your question... what happens in this case, it will select everything from the tables categories_parent and categories, when the id of the categories_parent is the same as id_categoria_parent of the table categories...; Sorry is confusing even, what is the name of your tables? Can you provide them in your question? So it would be easier to help;

  • But there is only one table, the table categories, within it has the field id and id_categoria_pai, ie the parent and daughter categories are in the same table

  • Ah... I understood, then it would be + or - like this (I would have done it separately, but okay, no problems). SELECT * FROM categories as cat1 LEFT JOIN categories as cat2 ON cat2.id = cat1.id_categoria_pai WHERE cat1.id=1; Test this sql and tell if it works... but I believe that for better maintenance it would be interesting to separate, (categories) and (subcategories);

  • It worked perfectly Rafael, thanks for your help, but I thought about what you said and I’ll break it up, thank you.

  • Perfect, I provided as an answer the solution so that it is not lost in case something happens with the comments;

Show 2 more comments

1 answer

2


Analyzing what the user provided information (parent and daughter categories are in the same table) the solution to the case would be more or less the following:

SELECT * FROM categorias as cat1 LEFT JOIN categorias as cat2 ON cat2.id = cat1.id_categoria_pai WHERE cat1.id=1;

However the interesting thing would be to separate the tables in categories and subcategories for better maintenance and understanding;

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.