CODEWITHSUNDEEP
mysqlsqldatabase-designcountjoin
Alireza Kafi
Alireza Kafi

Reputation: 13

join 3 tables and select count

i have 3 tables {websites} {accounts} {ads}

websites table

id     title    url           etc
-----------------------------------
1      site1    site1.com     ...
2      site2    site1.com     ...
3      site3    site3.com     ...

accounts table

id     websiteID   username    etc
-----------------------------------
1      1           username1   ...
2      2           username2   ...
3      1           username1   ...
4      3           username5   ...

ads table

id     accountID   title    etc
---------------------------------
1      1           title1   ...
2      2           title1   ...
3      1           title3   ...
5      4           title4   ...

i want to join these 3 table start from website table and get some data from website table , and accounts_count ralated to its website , and ads_count related to its account . also i want zero or null result for counts. usernames inside account table are not unique and can be same. titles inside ads table are not unique too and can be same.

this is my query but some times it return wrong result on counts ! 

SELECT 
    websites.id as website_id
,   websites.title as website_title
,   COUNT(accounts.websiteID) as accounts_count
,   COUNT(ads.accountID) as ads_count
,   ads.lastUpdate
,   websites.activation as website_activation 
FROM websites 
LEFT JOIN accounts 
    ON websites.id = accounts.websiteID 
LEFT JOIN ads 
    ON accounts.id = ads.accountID
GROUP BY websites.id;

can u help me :{

i want show this result in a table like this:

website_title     accounts_count   ads_count    last update  operations
-------------------------------------------------------------------------
website1           3               8            2017/07/27   etc...
website2           0               0            2017/07/27   etc...
website3           3               9            2017/07/27   etc...
website4           5               15           2017/07/27   etc...

Upvotes: 1

Views: 79

Answers (3)

LukStorms
LukStorms

Reputation: 29677

Seems that the counts need to change.
And a MAX for the ads.lastUpdate would be more accurate.

F.e.

SELECT 
    websites.id as website_id
,   websites.title as website_title
,   COUNT(DISTINCT accounts.ID) as accounts_count
,   COUNT(ads.ID) as ads_count
,   MAX(ads.lastUpdate) as LastUpdateAds
,   websites.activation as website_activation 
FROM websites 
LEFT JOIN accounts 
    ON websites.id = accounts.websiteID 
LEFT JOIN ads 
    ON accounts.id = ads.accountID
GROUP BY websites.id;

Upvotes: 1

Lê Tư Thành
Lê Tư Thành

Reputation: 1083

SELECT websites.id as website_id, websites.title as website_title,  
   ads.lastUpdate, websites.activation as website_activation 
   COUNT(accounts.websiteID) as accounts_count, COUNT(ads.accountID) as ads_count, 
FROM websites, accounts, ads
WHERE websites.id = accounts.websiteID 
   AND accounts.id = ads.accountID;

Upvotes: 0

dimo raichev
dimo raichev

Reputation: 580

The thing is when you join websites with accounts you get row for each row in accounts. Then when you join with ads you further multiplicate the row count. My guess is you now get same count for accounts_count and ads_count. Furthermore you have lastUpdate column from ads table and with agregate function.

Upvotes: 0

Related Questions