I am trying to do a complex (for me) query for a Grid application and have not been able to find the right solution. Any assistance would be appreciated.
For the Grid, I am working with the main table, and three N:N Relationship tables. I have found a solution for working with two of the three tables, but can not seem to integrate the third how I want.
Current query with 2 N:N tables with main table - works fine.
[B]Table 1 - main[/B]
1 A Title
[B]Table 2 - nn_dist (N:N table)[/B]
1 ABC
1 DEF
[B]Table 3 - nn_cp (N:N table)[/B]
1 123
1 456
1 789
SELECT
a.id,
a.title,
b.dist_id,
c.cp_id
FROM
main a
LEFT OUTER JOIN nn_dist b ON a.id = b.e_id
LEFT OUTER JOIN nn_cp c ON a.id = c.e_id
Produces
1 A Title ABC 123
1 A Title ABC 456
1 A Title ABC 789
1 A Title DEF 123
1 A Title DEF 456
1 A Title DEF 789
...
The above works great, but I need to merge in another table into the last column. Goal output:
[B]Table 4 - nn_non (N:N table)[/B]
1 111
1 222
1 A Title ABC 123
1 A Title ABC 456
1 A Title ABC 789
1 A Title ABC 111
1 A Title ABC 222
1 A Title DEF 123
1 A Title DEF 456
1 A Title DEF 789
1 A Title DEF 111
1 A Title DEF 222
...
I have tried CONCAT and COALESCE, but those just add two values together - such as “123111”. What other query methods are available so that the items in Table 4 are added on to the items in Table 3 so that my output shows all of the values? I have searched for “merge columns from two tables”, “combine columns”, etc. What term am I looking for?
I have messed with this for days. Any help would be appreciated.