Unknown User Unknown User - 4 months ago 7
SQL Question

Retrieve data from both tables one to many relationship in mysql

I've two table

Rule
and
Action
.

One rule can have many actions

lets say Action have a1,a2,a3 3 rows related to One Rule

then i want to select

r1.*, a1.name, a1.createdon, a1.modifiedon,
a2.name, a2.createdon, a2.modifiedon,
a3.name, a3.createdon, a3.modifiedon


but r2 may do not have any rule.
so it will select data from
Rule
only

result

r*


I've written this query

SELECT *
FROM Rule RB
LEFT JOIN (
SELECT RelatedRuleId
FROM Action_ GROUP BY RelatedRuleId
) R ON R.RelatedRuleId = RB.RuleId


this is not giving me correct result.

how can i pull this? I want one rule row with multiple actions in same row.

Answer

What you want is not achievable in plain SQL, because the number of columns in the result set has to be known before issuing the query. In your requirement, the number of columns is determined by the content of the tables. This is not possible in SQL.

What you can do, is either query the number of actions beforehand and dynamically build the SQL with a suitable number of columns in your application code or use group_concat to concat the result into one column like this:

select rule.id, 
       rule.name, 
       group_concat(concat(action.name, ';', 
                           action.createdon, ';', 
                           action.modifiedon)) as actions
from rule 
inner join action on rule.id = action.rule_id
group by rule.id

(see also the SQL fiddle http://sqlfiddle.com/#!9/94596/6 to play with it)

This yields something like:

id  name    actions
1   rule1   action1;2016-01-05;2016-02-06,action2;2016-01-06;2016-02-05
2   rule2   action3;2016-01-12;2016-02-19

Then you need to parse the resulting column by splitting it at the separators , and ;.