Relate Categories with Subcategories from the same table to another table

Question

Relate Categories with Subcategories from the same table to another table

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

2

Relate Subcategory Categories from the same table to another table

I have the following tables:

Sofar,Ithinkthemodelingisok,right?

ThenintheresultIwouldliketosearchforthefollowingfields:

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

Idonotwanttoconcatenatethecategoryandsubcategoryfields.

Even though I am joining the two tables, but the fields are not getting right, what I would like is that in the category field, it would be the name of the category and if it has subcategory the name of the subcategory would be in the field subcategory, consequently this for the id_categoria and id_subcategoria fields.

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

I would like to just do a Query with MySQL for this, if at all possible.

php mysql

asked by anonymous 20.05.2016 / 23:41

3 answers

Why in programs with sockets, does a TCP server always have more than one socket? InfoWindow is always the same in the markers all (javascript)

score 0 · Answer 1

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 :

score 0 · Answer 2

0

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

23.05.2016 / 13:46

score 0 · Answer 3

You need a relationship with nw_manuais_tecnicos_categorias and between itself to return the category PAI.

See 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 your own spoke can have category that has no parent.