Announcement

Collapse
No announcement yet.

Assistance with MySQL Query

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Assistance with MySQL Query

    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.
    Code:
    Table 1 - main
    1     A Title
    
    Table 2 - nn_dist (N:N table)
    1     ABC
    1     DEF
    
    Table 3 - nn_cp (N:N table)
    1     123
    1     456
    1     789
    Code:
    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
    Code:
    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:
    Code:
    Table 4 - nn_non (N:N table)
    1     111
    1     222
    Code:
    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.



  • #2
    I think you can use UNION ALL in the second LEFT JOIN

    Code:
    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 (SELECT c.id, c.cp_id FROM nn_cp c UNION ALL SELECT d.id, d.non_id FROM nn_non d) c ON a.id = c.e_id

    Comment


    • #3
      Originally posted by anjellz View Post
      I think you can use UNION ALL in the second LEFT JOIN
      You are absolutely right! UNION ALL was the perfect thing I was looking for. My query results exactly what I need. Your help is sincerely appreciated!

      Comment

      Working...
      X