Components and categories

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

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums.aspx/mysql-general/20070 2/1

Re: Components and categories

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.

On Feb 2, 2:27 am, "elektrongyorsito via DBMonster.com"
wrote:
> 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
>
> --
> Message posted via DBMonster.comhttp://www.dbmonster.com/Uwe/Forums.aspx/mysql- general/200702/1

Re: Components and categories

On Feb 2, 7:27 am, "elektrongyorsito via DBMonster.com"
wrote:
> 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
>
> --
> 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;