CODEWITHSUNDEEP
mysqljoinhyperlinkinner-join
Max Thorley
Max Thorley

Reputation: 173

Inner Join - Separate Columns

I'm trying to link 2 tables together containing multiple columns. For example I have a table called 'wp_users' which I would like to use as the main table, and the other table called 'CD2015_Occurrences'

I want to link 'wp_users' [EMAIL] to CD2015_Occurrences' [EMAIL] which is simple but I would like to then add inner joins.

So it would look something like this

Email | Q1 | Q2 | Q3 | Q4 |

email1 | 2 | 4 | 5 | 6

email2 | 4 | 5 | 6 | 0

etc...

Table contents of 'CD2015_Occurrences' are Email, Quarter, Total

I need to somehow link them by email to wp_users and have 4 separate columns for each quarter.

I'm probably WELL OFF but here is what I'm trying to do:

    Select a.user_email
FROM wp_users AS a
INNER JOIN a.email
ON CD2015_Occurrences.email as b (SELECT quarter AS Q1 FROM CD2015_Occurrences where email = a.email and quarter = 'q1')
INNER JOIN a.email
ON CD2015_Occurrences.email as b (SELECT quarter AS Q2 FROM CD2015_Occurrences where email = a.email and quarter = 'q2')
INNER JOIN a.email
ON CD2015_Occurrences.email as b (SELECT quarter AS Q3 FROM CD2015_Occurrences where email = a.email and quarter = 'q3')
INNER JOIN a.email
ON CD2015_Occurrences.email as b (SELECT quarter AS Q4 FROM CD2015_Occurrences where email = a.email and quarter = 'q4')

any ideas if this is possible and how? Many thanks Max

Upvotes: 0

Views: 44

Answers (1)

Alex
Alex

Reputation: 17289

SELECT users.email,
   SUM(IF(occurences.quarter='q1',occurences.total,0)) Q1,
   SUM(IF(occurences.quarter='q2',occurences.total,0)) Q2,
   SUM(IF(occurences.quarter='q3',occurences.total,0)) Q3,
   SUM(IF(occurences.quarter='q4',occurences.total,0)) Q4
FROM wp_users users
LEFT JOIN CD2015_Occurrences occurrences
ON users.email = occurrences.email
GROUP BY users.email

Upvotes: 1

Related Questions