Jump to content

[SOLVED] Join Question


n8w

Recommended Posts

Hello ... I am having trouble getting this sql statement to work the way I want.
I have a table where I track [b]"clicks" [/b]for the last 14 days for an artist ... but if they don't have any clicks they will not show up in the query.  So the problem is .. if they don't have any clicks with in the last 14 days they disappear from the database. I would like them to show up in the query .. but just to show the clicks as 0

I think the problem is the type of join I am using

[code]LEFT JOIN (SELECT user_id, COUNT(*) as clicks FROM external_url WHERE str_date > curdate() - INTERVAL 14 DAY GROUP BY user_id) c ON a.user_id=c.user_id [/code]

You can view the page at:
http://www.illustrationmundo.com/illustrators.php

Here is the entire sql statment
[code]
SELECT a.*, c.clicks, d.*,e.total_favorites,f.total_favorites_a,g.news_count FROM illustrators_table a
LEFT JOIN users d ON a.user_id = d.user_id
LEFT JOIN (SELECT user_id, COUNT(*) as clicks FROM external_url WHERE str_date > curdate() - INTERVAL 14 DAY GROUP BY user_id) c ON a.user_id=c.user_id
LEFT JOIN (SELECT illustrator_id,user_id, COUNT(illustrator_id) as total_favorites FROM favorites GROUP BY illustrator_id) e ON a.user_id=e.illustrator_id
LEFT JOIN (SELECT illustrator_id,user_id, COUNT(user_id) as total_favorites_a FROM favorites GROUP BY user_id) f ON a.user_id=f.user_id
LEFT JOIN (SELECT user_id, COUNT(user_id) as news_count FROM news_table GROUP BY user_id) g ON a.user_id=g.user_id WHERE visible ="t" && s_verified ="t" && a.feature IN ( "c" , "a" ) GROUP BY c.user_id
ORDER BY a.user_id DESC, feature DESC[/code]
Link to comment
https://forums.phpfreaks.com/topic/31350-solved-join-question/
Share on other sites

Thanks Fenway ... can you please tell me how/where to ad that? .. I have never done a NULL condition .. I assume I need to add it to


LEFT JOIN (SELECT user_id, COUNT(*) as clicks FROM external_url WHERE str_date > curdate() - INTERVAL 14 DAY GROUP BY user_id) c ON a.user_id=c.user_id
Link to comment
https://forums.phpfreaks.com/topic/31350-solved-join-question/#findComment-145330
Share on other sites

I looked at the query again, and you're never checking against any column in c (maybe the group by, which sould probably be by u.user_id anyway), so it should just get nulled out, but you should still get all users that appear in the illustrators table at the very least.
Link to comment
https://forums.phpfreaks.com/topic/31350-solved-join-question/#findComment-145457
Share on other sites

ahh .. I finally figured out the problem .. which is what I think you might have been saying ... all I had to do is change the group by to a instead of c

wrong
LEFT JOIN (SELECT user_id, COUNT(user_id) as news_count FROM news_table GROUP BY user_id) g ON a.user_id=g.user_id WHERE visible ="t" && s_verified ="t" && a.feature IN ( "c" , "a" ) GROUP BY c.user_id

right
LEFT JOIN (SELECT user_id, COUNT(user_id) as news_count FROM news_table GROUP BY user_id) g ON a.user_id=g.user_id WHERE visible ="t" && s_verified ="t" && a.feature IN ( "c" , "a" ) GROUP BY a.user_id
Link to comment
https://forums.phpfreaks.com/topic/31350-solved-join-question/#findComment-146512
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.