2

I need to export channel entries with matrix data and having problems joining the entry id with my matrix subquery

This is the matrix subquery which adds up my rows:

SELECT exp_matrix_data.entry_id, sum(exp_matrix_data.col_id_13) as TotalPartyPoints, sum(exp_matrix_data.col_id_14) as RedeemablePoints  
FROM exp_matrix_data WHERE  exp_matrix_data.entry_id = 60

These are the other fields:

SELECT
        exp_channel_data.entry_id as ID, 
        exp_members.username as Email, 
        exp_channel_data.field_id_27 as FirstName, 
        exp_channel_data.field_id_28 as LastName, 
        exp_channel_data.field_id_29 as Gender, 
        exp_channel_data.field_id_31 as Address, 
        exp_channel_data.field_id_32 as City, 
        exp_channel_data.field_id_33 as State, 
        exp_channel_data.field_id_34 as ZipCode, 
        exp_channel_data.field_id_35 as CellPhone,
        exp_channel_data.field_id_36 as AltPhone,
        exp_channel_data.field_id_37 as SpouseName,
        FROM_UNIXTIME(exp_channel_titles.entry_date, '%m/%d/%Y') as InceptionDate,
        exp_channel_data.field_id_39 as Country,
        exp_channel_data.field_id_40 as GoldClub,
        exp_channel_data.field_id_43 as KAMA,
        exp_channel_titles.entry_id,
        exp_channel_titles.author_id,
        exp_channel_titles.channel_id
        FROM  exp_channel_data, exp_channel_titles, exp_members
        WHERE  exp_channel_data.entry_id = exp_channel_titles.entry_id
        AND exp_channel_titles.channel_id = 6
        AND exp_members.member_id = exp_channel_titles.author_id
        ORDER by LastName asc

Both selects return the desired results. How can I join my matrix query here as a subquery?

1

Try this query:

SELECT exp_channel_data.entry_id as ID, 
SUM(exp_matrix_data.col_id_13) as TotalPartyPoints, 
SUM(exp_matrix_data.col_id_14) as RedeemablePoints, 
exp_members.username as Email, 
exp_channel_data.field_id_27 as FirstName, 
exp_channel_data.field_id_28 as LastName, 
exp_channel_data.field_id_29 as Gender, 
exp_channel_data.field_id_31 as Address, 
exp_channel_data.field_id_32 as City, 
exp_channel_data.field_id_33 as State, 
exp_channel_data.field_id_34 as ZipCode, 
exp_channel_data.field_id_35 as CellPhone, 
exp_channel_data.field_id_36 as AltPhone, 
exp_channel_data.field_id_37 as SpouseName, 
FROM_UNIXTIME(exp_channel_titles.entry_date, '%m/%d/%Y') as InceptionDate, 
exp_channel_data.field_id_39 as Country, 
exp_channel_data.field_id_40 as GoldClub, 
exp_channel_data.field_id_43 as KAMA, 
exp_channel_titles.entry_id, 
exp_channel_titles.author_id, 
exp_channel_titles.channel_id 
FROM exp_channel_titles
JOIN exp_channel_data ON exp_channel_data.entry_id=exp_channel_titles 
JOIN exp_members ON exp_members.member_id=exp_channel_titles.author_id  
JOIN exp_matrix_data ON exp_matrix_data.entry_id=exp_channel_titles.entry_id  
WHERE exp_channel_titles.channel_id = 6 GROUP BY exp_matrix_data.entry_id ORDER by exp_channel_data.field_id_28 ASC 

I hope, it would work for you.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.