CODEWITHSUNDEEP
phpmysqljoininner-join
Morten Hagh
Morten Hagh

Reputation: 2113

Adding a join in existing statement

I have an SQL statement with multiple joins that works without any problems at all

$sql = "SELECT ni.*, GROUP_CONCAT(DISTINCT newsletter_item_receivers.value ORDER BY newsletter_item_receivers.value) AS receivers, nf.*, GROUP_CONCAT(DISTINCT nm.mailgroup_name ORDER BY nm.mailgroup_name) AS mailgroups
        FROM newsletter_items ni
        INNER JOIN newsletter_fields nf ON (nf.field_letter_uid = ni.letter_id)                    
        LEFT JOIN newsletter_item_receivers ON (newsletter_item_receivers.letter_id = ni.letter_id)
        INNER JOIN newsletter_mailgroups nm ON (FIND_IN_SET(nm.mailgroup_id, newsletter_item_receivers.value))            
        WHERE nf.field_name = 'letter_headline' AND ni.template = '". $template ."' AND deleted = 0
        GROUP BY ni.letter_id

What I want is to join data from newsletter_clickstat that contain ifo about links on a specific newsletter (such as number of clicks on the link).

I tried to insert 

LEFT JOIN newsletter_clickstat cs ON (cs.letter_id = ni.letter_id)

But that didn't do anything

SOLVED:

    SELECT ni.*, GROUP_CONCAT(DISTINCT newsletter_item_receivers.value ORDER BY newsletter_item_receivers.value) AS receivers, nf.*, GROUP_CONCAT(DISTINCT nm.mailgroup_name ORDER BY nm.mailgroup_name) AS mailgroups, cs.*
    FROM newsletter_items ni
    INNER JOIN newsletter_fields nf ON (nf.field_letter_uid = ni.letter_id)                    
    LEFT JOIN newsletter_item_receivers ON (newsletter_item_receivers.letter_id = ni.letter_id)
    INNER JOIN newsletter_mailgroups nm ON (FIND_IN_SET(nm.mailgroup_id, newsletter_item_receivers.value)) 
    LEFT JOIN newsletter_clickstat cs ON (cs.letter_id = ni.letter_id)                 
    WHERE nf.field_name = 'letter_headline' AND ni.template = '". $template ."' AND deleted = 0
    GROUP BY ni.letter_id

Upvotes: 0

Views: 85

Answers (1)

eggyal
eggyal

Reputation: 125925

(Upgrading to an answer)

You also need to select the columns from newsletter_clickstat which you would like in your resultset... bear in mind that you're performing a grouping operation so will either need to apply an aggregate function or change the columns over which you perform your group.

Upvotes: 1

Related Questions