CODEWITHSUNDEEP
mysqlsql
Richard
Richard

Reputation: 1259

Table join issue with MySQL

I have a table for referred users (contains an email address and date columns) and a table for users.

I run to get the top referers: 

SELECT count(r.Email) as count, r.Email 
FROM refs r 
WHERE r.referredOn > '2011-12-13' 
GROUP BY email 
ORDER BY count DESC

But I want to join this with the users table so it displays with other data in the user table, I thought a join would work. Left join becuase emails may be entered incorrectly, some people put first name etc under refs.Email

SELECT count(r.Email) as count, r.Email, u.* 
FROM refs r LEFT JOIN users u ON u.email_primary = r.Email 
WHERE r.referredOn > '2011-12-13' 
GROUP BY email 
ORDER BY count DESC

With the above query the count is incorrect, but I don't know why.

Upvotes: 1

Views: 78

Answers (4)

Richard
Richard

Reputation: 1259

I wrote this query

SELECT *, count(r.Email) as count FROM refs r 
LEFT OUTER JOIN users u ON r.email = u.email_primary
WHERE u.uid IS NOT NULL
GROUP BY u.uid
ORDER BY count DESC

Which showed me that the reason the count was wrong was because some of the email addresses are used twice in the users table (users sharing 'family' email address), this doubled my count, the above query shows each separate user account.

Upvotes: 0

Nonym
Nonym

Reputation: 6299

SEE if this will help you:

SELECT e.count, e.email, u.col1, u.col2 -- etc
FROM (
    SELECT count(r.Email) as count, r.Email 
    FROM refs r 
    WHERE r.referredOn > '2011-12-13' 
    GROUP BY email 
) e 
INNER JOIN 
users u ON u.email_primary = e.Email

Instead of a direct join, you could TRY to use your counting query as a subquery-table type..

Upvotes: 0

BizApps
BizApps

Reputation: 6130

Try this one:

SELECT count(r.Email) as count, r.Email 
FROM refs r 
INNER JOIN users u ON u.email_primary = r.Email 
WHERE r.referredOn > '2011-12-13' 
GROUP BY email 
ORDER BY count DESC

if your adding new column from users u you also need to add it on your group by clause.

Regards

Upvotes: 1

dash
dash

Reputation: 91540

Unfortunately, a LEFT JOIN wont help you here; what this type of join says is give me all the rows in users that match my email, as well as all the rows that have no match on email. If the email doesn't match, then they wont come back as you want.

So you can't use a the left join condition here the way you want.

If you enforced the fact that they had to enter an email everytime, and it was a valid email etc, then you could use an INNER JOIN.

JOINs are usually used to follow referential integrity. So, for example, I have a user with an id in one table, and another table with the column userid - there is a strong relationship between the two tables I can join on.

Jeft Atwood has a good explantion of how joins work.

Upvotes: 0

Related Questions