Multiple Defninitions in WHERE Clause in subquery

Dev Shed Forums Sponsor:

September 17th, 2012, 05:47 PM

wad3brown

Registered User

Join Date: Sep 2012

Posts: 2

Time spent in forums: 52 m 32 sec
Reputation 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.

September 17th, 2012, 06:30 PM

Guelphdad

Hockey face

Dev Shed God 7th Plane (8000 - 8499 posts)

Join Date: Nov 2001

Location: St. Catharines, Canada

Posts: 8,141

Time spent in forums: 2 Months 3 Weeks 3 Days 20 h 34 m 13 sec
Reputation Power: 1315

Can't you simply use GROUP_CONCAT_WS to do that?

September 19th, 2012, 06:32 PM

wad3brown

Registered User

Join Date: Sep 2012

Posts: 2

Time spent in forums: 52 m 32 sec
Reputation Power: 0

Quote:

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.

Multiple Defninitions in WHERE Clause in subquery

Developer Shed Advertisers and Affiliates