Home mysql Many to Many Relationship as Columns
Reply: 0

mysql Many to Many Relationship as Columns

user2578
1#
user2578 Published in May 23, 2018, 10:41 pm

I have the following structure:

case:
id int
...

behaviours:
id int,
label varchar

case_to_behaviour:
case_id,
behaviour_id

The Behaviours is a set list, it does not get dynamically added or edited or deleted by the users.

there are a number of Many to Many relationships like this which i need to report on, but getting this one right would give me a template to work with the rest of them.

So far, because there is only 8 listed 'behaviours' i have the following query which gets me what i want, how ever i am hoping maybe there is a nicer or more efficient way to query this.

The data need to be present like so:

id | behaviour_1 | behaviour_2 | behaviour_3 | behaviour_4 | behaviour_5 ....
----------------------------------------------------------------------------
1  | true        | true        | false       | false       | true

The script which i currently use to get his is as followed, but will only work up to 9 possible items.

SELECT 
    case.id,
    IF(csb.behaviours like '%1%', true, false) as `behaviour_1`,
    IF(csb.behaviours like '%2%', true, false) as `behaviour_2`,
    IF(csb.behaviours like '%3%', true, false) as `behaviour_3`,
    IF(csb.behaviours like '%4%', true, false) as `behaviour_4`,
    IF(csb.behaviours like '%5%', true, false) as `behaviour_5`,
    IF(csb.behaviours like '%6%', true, false) as `behaviour_6`,
    IF(csb.behaviours like '%7%', true, false) as `behaviour_7`,
    IF(csb.behaviours like '%8%', true, false) as `behaviour_8`

from case c
left join 
    (select group_concat(behaviour_id) as behaviours, case_id  from case_to_behavior group by case_id) csb on csb.case_id=c.id
group by c.id;

EDIT: I have tried a similar query as demonstraight below, how ever it only works for the first join, which makes sense. i just don't know how to get the data in the format i want.

SELECT 
    case.id,
    IF(csb.behaviour_id = 1, true, false) as `behaviour_1`,
    IF(csb.behaviour_id = 2, true, false) as `behaviour_2`,
    IF(csb.behaviour_id = 3, true, false) as `behaviour_3`,
    IF(csb.behaviour_id = 4, true, false) as `behaviour_4`,
    IF(csb.behaviour_id = 5, true, false) as `behaviour_5`,
    IF(csb.behaviour_id = 6, true, false) as `behaviour_6`,
    IF(csb.behaviour_id = 7, true, false) as `behaviour_7`,
    IF(csb.behaviour_id = 8, true, false) as `behaviour_8`,
from case c
left join case_to_behavior csb on csb.case_id=c.id
group by c.id; 
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.25026 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO