CODEWITHSUNDEEP
phpmysql
user2054962
user2054962

Reputation: 31

MySQL Using UNION or OUTER LEFT JOIN

I have been struggling with what seems a simple enough query. I have three tables, Accounts, Contacts & Activities. Everytime an Activity is logged it logs the AccountID, Contact Subject, TimeDate.

I query the Activities table to show me all the activity count from the previous week for each AccountID.

I use:

select
accounts.account as Account,
count(distinct activities.contactid) as Users,
from accounts, activities
where activities.accountid=accounts.accountid
AND completeddate >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND completeddate < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
group by accounts.account asc;

The result is something like This: 

Account      Users
ACME Ltd     4
Warner Bros  6
RBS          9

etc..

The activities table has baout 20 million rows and this runs in about 20 seconds.

However, I want a comprehensive list. I want to combine the results with a list of AccountID's that havent had any activity for that month. 

Account      Users
ACME Ltd     4
Warner Bros  6
RBS          9
Microsoft    0  or NULL

etc...

i have tried to UNION like this:

select Account, '' from Accounts
UNION
select
accounts.account as Account,
count(distinct activities.contactid) as Users
from accounts, activities
where activities.accountid=accounts.accountid
AND completeddate >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND completeddate < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
group by accounts.account asc;

From what i understand about UNION is that it should return a unique list (without duplicates). But what i get is a list of aprox 1400 accounts with when I only have approx 900 Accounts.

I have tried LEFT OUTER JOIN but this just seemed to run forever ( i killed it after 2 hours)

Does anyone have any suggestions on what I can try?

Thanks

Upvotes: 0

Views: 226

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

Is this the left outer join that you tried?

select accounts.account as Account,
       count(distinct activities.contactid) as Users
from accounts left outer join
     activities
     on activities.accountid=accounts.accountid
where completeddate >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY and
      completeddate < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
group by accounts.account asc;

This should have about the same performance as your original query.

By the way, you should always use the explicit join syntax (join keyword in the from clause), rather than an implicit join in the where clause.

It seems that completeddate is in the activities table (always use aliases). This needs to be moved into the on clause in that case:

select accounts.account as Account,
       count(distinct activities.contactid) as Users
from accounts left outer join
     activities
     on activities.accountid=accounts.accountid and
        activities.completeddate >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY and
        activities.completeddate < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
group by accounts.account asc;

Upvotes: 1

Adder
Adder

Reputation: 5868

It depends on whether you want to combine the selects with "or" or "and" - logic.

For "or" use UNION DISTINCT for "and" use INNER JOIN on the primary key.

Upvotes: 0

Related Questions