MySQL Join the Best Option Here?

campaigns:

id     name            cap
1      Campaign1       2
2      Campaign2       1
3      Campaign3       1

served:

id     id_campaign     ip
1      1               127.0.0.1
2      1               127.0.0.1
3      2               127.0.0.1

The result of the query should display:

campaigns_id    campaigns_name          cap    count
1               Campaign1               2      2
2               Camp开发者_高级运维aign2               1      1
3               Campaign3               1      0

I am using this query:

   SELECT served.id_campaign, 
          campaigns.name, 
          campaigns.cap, 
          COUNT( served.id ) AS count
     FROM campaigns
LEFT JOIN served ON campaigns.id = served.id_campaign
    WHERE served.ip =  '127.0.0.1' 
 GROUP BY served.id_campaign

My query is displaying these results (bad, not showing campaigns_id.3):

campaigns_id    campaigns_name          cap    count
1               Campaign1               2      2
2               Campaign2               1      1

I am thinking I am approaching wrong if I want to include values that aren't being selected by the "WHERE" statement as there are no records in "served" that match the WHERE statement for campaigns.id='3'

---

NEW EDIT:

   SELECT campaigns.id as id_campaign, 
          campaigns.name, 
          campaigns.cap, 
          COUNT( served.id ) AS count
     FROM campaigns
LEFT JOIN served ON campaigns.id = served.id_campaign
      AND served.ip =  '127.0.0.1' 
 GROUP BY campaigns.id

OLD POST:

I came up with this query, which produces the result you want:

(SELECT served.id_campaign, 
          campaigns.name, 
          campaigns.cap, 
          COUNT( served.id ) AS count
     FROM campaigns
LEFT JOIN served ON campaigns.id = served.id_campaign
    WHERE served.ip =  '127.0.0.1' 
 GROUP BY served.id_campaign)

UNION

(SELECT id, name, cap, 0 as count 
FROM campaigns 
WHERE id <> ALL 
     (
      SELECT campaigns.id 
      FROM campaigns, served 
      WHERE campaigns.id = served.id_campaign AND served.ip = "127.0.0.1"
     )
)

---

Change your where clause to

WHERE served.ip =  '127.0.0.1' OR served.ip IS NULL

---

LEFT JOIN says that bring all the records from left table and the matching records from the right hand side table. So the served.ip would be NULL from id=3. So change the where condition to make it

WHERE serverd.ip = '127.0.0.1' or serverd.ip IS NULL