Components and categories

[elektrongyorsito via DBMonster.com]

Hello,


I have 3 tables:

T_component =TCO
cmp_id | name
1 | door
2 | exhaust

T_cmp_cat =TCC (categories and components)
id | cmp_id | cat_id
1 | 1 | 1
2 | 1 | 3
3 | 1 | 4
4 | 2 | 1
5 | 2 | 2

T_categories =TCA
cat_id | cat_name
1 | car
2 | motorcycle
3 | train
4 | airplane
5 | other


If I ask this:
select name,cat_name from TCO inner join TCC on TCO.cmp_id=TCC.cmp_id inner
join TCA on TCA.cat_id=TCC.cat_id

then I get, like this:

name | cat_name
door | car
door | train
door | airplane
exhaust |car
exhaust |motorcycle

But, I would like to get this:

Name | cat_car | cat_motorcycle | cat_train | cat_airplane | cat_other
door | 1 | 0 | 1 | 1 | 0
exhaust | 1 | 1 | 0 | 0 | 0

Is there any simple way to get?

When I created the db, I asked more poeple about this, and they suggested
this form, because flexible expanding of the categories. But now I think I
could work easier if I store the categories into the component table, like
this:

T_component =TCO
cmp_id | name | cat1 | cat2 ...
1 | door | 0 | 1 |...
2 | exhaust | 1 | 1 |...

But it displease me... How can I solve it correctly? Thank you

[t***@yahoo.com]

What version of MySQL are you using? I'm asking because the only way
I can think of solving this is with an embedded SELECT statement....
which earlier versions of MySQL did not know how to handle.

Here is a general mockup of what I think may work for you:
SELECT name, (SELECT cat_id FROM T_cmp as b WHERE b.cmp_id = a.cmp_id
AND b.cat_id = 1) AS cat_car, etc...
FROM T_component AS a

I haven't tested this at all... it's just an idea.

Post by elektrongyorsito via DBMonster.com
Hello,
T_component =TCO
cmp_id | name
1 | door
2 | exhaust
T_cmp_cat =TCC (categories and components)
id | cmp_id | cat_id
1 | 1 | 1
2 | 1 | 3
3 | 1 | 4
4 | 2 | 1
5 | 2 | 2
T_categories =TCA
cat_id | cat_name
1 | car
2 | motorcycle
3 | train
4 | airplane
5 | other
select name,cat_name from TCO inner join TCC on TCO.cmp_id=TCC.cmp_id inner
join TCA on TCA.cat_id=TCC.cat_id
name | cat_name
door | car
door | train
door | airplane
exhaust |car
exhaust |motorcycle
Name | cat_car | cat_motorcycle | cat_train | cat_airplane | cat_other
door | 1 | 0 | 1 | 1 | 0
exhaust | 1 | 1 | 0 | 0 | 0
Is there any simple way to get?
When I created the db, I asked more poeple about this, and they suggested
this form, because flexible expanding of the categories. But now I think I
could work easier if I store the categories into the component table, like
T_component =TCO
cmp_id | name | cat1 | cat2 ...
1 | door | 0 | 1 |...
2 | exhaust | 1 | 1 |...
But it displease me... How can I solve it correctly? Thank you
--
Message posted via DBMonster.comhttp://www.dbmonster.com/Uwe/Forums.aspx/mysql-general/200702/1

[strawberry]

Post by elektrongyorsito via DBMonster.com
Hello,
T_component =TCO
cmp_id | name
1 | door
2 | exhaust
T_cmp_cat =TCC (categories and components)
id | cmp_id | cat_id
1 | 1 | 1
2 | 1 | 3
3 | 1 | 4
4 | 2 | 1
5 | 2 | 2
T_categories =TCA
cat_id | cat_name
1 | car
2 | motorcycle
3 | train
4 | airplane
5 | other
select name,cat_name from TCO inner join TCC on TCO.cmp_id=TCC.cmp_id inner
join TCA on TCA.cat_id=TCC.cat_id
name | cat_name
door | car
door | train
door | airplane
exhaust |car
exhaust |motorcycle
Name | cat_car | cat_motorcycle | cat_train | cat_airplane | cat_other
door | 1 | 0 | 1 | 1 | 0
exhaust | 1 | 1 | 0 | 0 | 0
Is there any simple way to get?
When I created the db, I asked more poeple about this, and they suggested
this form, because flexible expanding of the categories. But now I think I
could work easier if I store the categories into the component table, like
T_component =TCO
cmp_id | name | cat1 | cat2 ...
1 | door | 0 | 1 |...
2 | exhaust | 1 | 1 |...
But it displease me... How can I solve it correctly? Thank you
--
Message posted via DBMonster.comhttp://www.dbmonster.com/Uwe/Forums.aspx/mysql-general/200702/1

If this is as complicated as it gets, then a query like the one below
should work. However, you may want to investigate pivot tables and
stored procedures, or alternatively, finding a php-based solution.

SELECT name, count( A.cat_id ) car, count( B.cat_id ) motorcyle,
count( C.cat_id ) train, count( D.cat_id ) airplane, count( E.cat_id )
other
FROM TCO
LEFT JOIN TCC A ON A.cmp_id = TCO.cmp_id
AND A.cat_id =1
LEFT JOIN TCC B ON B.cmp_id = TCO.cmp_id
AND B.cat_id =2
LEFT JOIN TCC C ON C.cmp_id = TCO.cmp_id
AND C.cat_id =3
LEFT JOIN TCC D ON D.cmp_id = TCO.cmp_id
AND D.cat_id =4
LEFT JOIN TCC E ON E.cmp_id = TCO.cmp_id
AND E.cat_id =5
GROUP BY 1;