#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0

    Multiple Defninitions in WHERE Clause in subquery


    I'm having to join over 100 fields into one field to import our mailing lists into MS CRM 4.0.

    I've created a query that works, but I'm wondering if there's a way to simplify it so there isn't so much duplicate content.

    You'll see at the top
    Code:
    IFNULL(list169_subquery.pro_id,''),'|'
    I need to call 100 of those and each has it's own subquery. Is there a way to combine all those subqueries into one with a more specific WHERE clause?

    Code:
    SELECT
        ct.id AS ContactsID,
        ct.isActive,
        CONCAT ( '|',
        IFNULL(list169_subquery.pro_id,''),'|',
        IFNULL(list248_subquery.pro_id,''),'|',
        ) AS MailingLists
        
    FROM
        cmd.contacts AS ct
        
    LEFT JOIN
    
    (SELECT
        pros.isActive
        , ct.id
        , ct.firstName
        , ct.lastName
        , pros.id AS pro_id
        , pros.projectName
        , pro_link.contactId
    FROM
        cmd.project_contact_link AS pro_link
        INNER JOIN cmd.contacts AS ct 
            ON (pro_link.contactId = ct.id)
        INNER JOIN cmd.projects AS pros
            ON (pro_link.projectId = pros.id)
    WHERE (pros.isActive =1 AND pros.id = 169) ) AS `list169_subquery` ON `list169_subquery`.contactId = ct.id 
    
    LEFT JOIN
    
    (SELECT
        pros.isActive
        , ct.id
        , ct.firstName
        , ct.lastName
        , pros.id AS pro_id
        , pros.projectName
        , pro_link.contactId
    FROM
        cmd.project_contact_link AS pro_link
        INNER JOIN cmd.contacts AS ct 
            ON (pro_link.contactId = ct.id)
        INNER JOIN cmd.projects AS pros
            ON (pro_link.projectId = pros.id)
    WHERE (pros.isActive =1 AND pros.id = 248) ) AS `list248_subquery` ON `list248_subquery`.contactId = ct.id
    
    WHERE (ct.isActive =1);
    In the end that takes several Mailing List numbers and puts themm all together and separates them by a | symbol.

    Code:
    ContactsID  |169|248|
    Thanks for any help, I'm sure it's probably something basic I don't understand yet.
  2. #2
  3. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1317
    Can't you simply use GROUP_CONCAT_WS to do that?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by Guelphdad
    Can't you simply use GROUP_CONCAT_WS to do that?
    You're right, that did it. I needed to GROUP_CONCAT all the results of that field in 1 subquery and then join it to the main query.
    Code:
    SELECT
        ct.id
        , pro_link.projectId
        , pros.isActive
        , GROUP_CONCAT(pros.id SEPARATOR '|')
    FROM
        cmd.contacts AS ct
        INNER JOIN cmd.project_contact_link AS pro_link 
            ON (ct.id = pro_link.contactId)
        INNER JOIN cmd.projects AS pros
            ON (pro_link.projectId = pros.id)
            GROUP BY ct.id;
    Thanks, still learning.

IMN logo majestic logo threadwatch logo seochat tools logo