List Categories with Subcategories of the same table with another table

Asked

Viewed 558 times

2

List Categories with Subcategories of the same table with another table

I have the following tables:

Screen_Hunter_15_May_20_10_44.png

So far I believe the modeling is ok, right?

Then in the result I would like to search the following fields:

id_manual | titulo | arquivo | downloads | id_categoria | id_subcategoria | categoria | subcategoria -

Since I would not like to concatenate the category and subcategory fields.

Screen_Hunter_15_May_20_10_47.png

I am even doing the merging of the two tables, but the fields are not getting right, what I would like is that in the category field, the name of the category and if you have subcategory the name of the subcategory is in the subcategory field, therefore this for the fields id_categoria and id_subcategoria.

How could I do this without concatenating the fields (doing with separator fields)? or just filtering using PHP?

I would like to do only one Query with Mysql for this, if possible.

php mysql
  • 1

    Possible duplicate of Sort by category

  • @Guilhermespinxo How could it be done without concatenating the result as in the image 2 I posted? worth

  • How do you mean concatenate the result? Explain it better so I can try to help you.

  • @Guilhermespinxo in the post you mentioned you use a Sub-select which returns a column with multiple records using Group_concat, what I would like is for you to return the two results in separate columns, example: return the category name and subcategory in separate fields. I don’t know if that would be possible. Do you have any idea?

  • You speak of subcategory but I have not seen this field or ID in both tables, I only saw id_category and id_categoria_pai. Where subcategory is?

3 answers

0

What you want can be achieved with the use of joins. Example:

select * from manuais_categorias as m 
left join manuais_categorias as n on m.id = n.id_pai 
LEFT JOIN manuais_tecnicos on manuais_tecnicos.id_categoria = m.id;

Example in SQL Fiddle:

  • I tried to make this code, but with several records the same comes duplicated.

  • From the comments of the previous answer I think that answer is no longer appropriate. But complementing, to solve the problem of duplicity just change the second LEFT JOIN for INNER JOIN. With this approach not all categories will be shown, only those that have an associated manual. See edition at [SQL Fiddle][1]: [1]: http://sqlfiddle.com/#! 9/9c869/2/1

  • Unfortunately continues to repeat data, I put here an SQL to better exemplify, I did what you had passed, a check: http://sqlfiddle.com/#! 9/986fcf/6

0

You need a relationship with nw_manuais_tecnicos_categorias and between itself to returns the category FATHER.

Take the example.

select m.id_manual , m.titulo , m.arquivo , m.downloads ,
Ca.id_categoria , CaPai.id_categoria as id_subcategoria , Ca.Nome as categoria ,  CaPai.Nome as subcategoria 
from nw_manuais_tecnicos as m 
join nw_manuais_tecnicos_categorias as Ca on Ca.id_categoria = m.id_categoria 
left JOIN nw_manuais_tecnicos_categorias as CaPai on CaPai.id_categoria = Ca.id_categoria_pai;

Here I use the left Join as you yourself spoke can have category that has no father.

0

SELECT * FROM nw_manuais_tecnicos AS mt
LEFT JOIN nw_manuais_tecnicos_categorias as mc USING (id_categoria)
GROUP BY mt.id_categoria

  • With this code, the result ends up repeating several times.

  • Did you use GROUP BY? If it didn’t work with m.id_pai, try manuals_tecnicos.id_categoria

  • Same result if you want to check: [link]http://s33.postimg.org/cokczojm7/Screen_Hunter_15_May_23_09_56.png[link]

  • you can generate its SQL?

  • select * from manuals_categories as m LEFT JOIN manuais_tecnicos on manuais_tecnicos.id_categoria = m.id; GROUP BY m.id_categoria

  • Try it this way....

  • I modified my answer, try it this way...

  • http://sqlfiddle.com/#! 9/2dfe8f/1

  • I put the SQL code here, a check, I could not with its code: http://sqlfiddle.com/#! 9/986fcf/3

  • I just copied the code here and ran it there on your sqlfiddle, it worked out apparently.. see ai http://sqlfiddle.com/#! 9/986fcf/4

  • 1

    Actually it is not yet right hehehe.. Look at this, in the column name, the record "Building multiple consumer units" is a parent Category, right? in the later record "Test 2", it is a subcategory of the parent category "Building of multiple consumer units", so the correct one would be to have the columns Category with "Building of multiple consumer units" in the two records, and the column subcategory with the record "Test 2" only on the last line. I don’t know if I could explain it properly.

Show 6 more comments

Browser other questions tagged php mysql

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