Hi,

Yes I have made some mistakes.  There was problem with the outer
join.  It should have been ad outer joined to review, not the other way.
I didn't notice the first tabel person in the query.

Try the following with two tables ad and review and later add person
table.  We don't know the columns of person table to relate with the
othere tables.

select ad.id,sum(review.id is not null) from
ad left join review on ad.id=review.id
group by ad.id;
OR
select ad.id,sum(if (ifnull(review.id,0)=0,0,1)) from
ad left join review on ad.id=review.id
group by ad.id;

>the id column can't be null, it's defined as varchar(16) not null.

Even though the id column is defined as not null and there is no
null values in this field of review table, the value returned by the
query for this column can be null for outer joins.  In fact we use
outer joins to return a row even if there is no rows in the second
(right table in the outer join ) with all the columns of this row
with null value.

The above query (first) behaves like this:

It will return one row each for each row in review table which has
a corresponding id in ad table.
Additionally it will return one row for each row in ad table for which
there is no corresponding row in review table but with review column
value as null.

So for each id in ad for which there is no reviews the review.id will be
null and the expression (review.id is not null) will return 0, for which
there is review it will return 1.  Hence summing this on this expression
should give you the right answer.

Anvar.



---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Reply via email to