Easy query problem ..:-) but not for me...

[Perttu Pulkkinen]

Hi!

I have two (very typical) tables like advertisers and ads.
How can I get two things in same query:

1) All info about certain (or every) advertiser
2) Number of ads he/she has in our service

I have tried something similar to this:

Select name, address, count(ads)
from advertisers, ads
where advertiser_id = ad_arvertiser_id

but I remember I didn't got it to work.

Thanks for your help!

perttu pulkkinen, finland

[Aggro]

Perttu Pulkkinen wrote:

I have two (very typical) tables like advertisers and ads.
How can I get two things in same query:

1) All info about certain (or every) advertiser
2) Number of ads he/she has in our service

select
table1.id,
if( table2.id is null,0,count(*) ) as amount
from table1
left join table2 on table1.id=table2.id
group by table1.id;

This will give you results like this:

+------+--------+
| id | amount |
+------+--------+
| 1 | 6 |
| 2 | 1 |
| 4 | 0 |
| 5 | 0 |
| 7 | 0 |
| 8 | 0 |
+------+--------+

[Perttu Pulkkinen]

Thanks aggro - Finland salutes Finland!