[SOLVED] Join Question

[n8w]

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]

[fenway]

That's strange.. you're LEFT JOINing, and there's no WHERE clause on the non-joined table, so you should get everyone back.

[fenway]

Actually, now that I think about it, you may be missing a OR ... IS NULL condition in one of those joins...

[n8w]

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

[fenway]

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.

[n8w]

Hum .. I am a bit confused .. what table would u.user_id be referring to?
Where in the sql statement would I modify it?
thanks

[n8w]

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