CODEWITHSUNDEEP
sql
cute
cute

Reputation: 21

sql left join with where confusion

This is my current sql query

SELECT 
  urls.short, 
  urls.long, 
  urls.user, 
  dates.date AS date, 
  COUNT(views.short) AS views, 
  SUM(views.money) AS money 
FROM dates 
LEFT JOIN views ON dates.date = views.date
LEFT JOIN urls ON views.short = urls.short
WHERE urls.user='test'
GROUP BY dates.date, views.date
LIMIT 0, 20

It show results something like this :

2011-02-08 test test 1 0.003

2011-02-09 test test 6 0.016

But when i remove 

WHERE urls.user='test'

I get

2011-02-05 0

2011-02-06 0

2011-02-07 44kc http://google.com 27 0.031

2011-02-08 44kc http://google.com 15 0.045

2011-02-09 test test 12 0.032

2011-02-10 0

so finally i want to get the second query result using first query(to show 0 ,when there are nothing available). So whats the solution?

Upvotes: 2

Views: 3433

Answers (1)

Oswald
Oswald

Reputation: 31647

I am not sure if I understand your question correctly, but you might want to consider

LEFT JOIN urls ON views.short = urls.short AND urls.user='test'

Upvotes: 10

Related Questions