sql subquery channel entry & matrix rows

Question

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?

Answer 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.