>I have a problem with a few sql-queries because I have written the
>with subselect statements and then will they not work on older MySQL
>Can any one help me to say haw to solve it without subselect!
>Today looks the questions like this:
> (SELECT count(SUB.S_ID)
> FROM SUB
> LEFT JOIN SUBS ON SUBS.S_ID = SUB.S_ID)
> /(SELECT COUNT(*) FROM SUB);
Two problems, the query doesn't parse and its intention isn't entirely
clear. If it is meant to return the ratio...
(no. of sub rows with non-null s_id values) / (total no. of sub rows)
then this would work...
SELECT COUNT(s_id) / COUNT(*)
>SELECT COUNT(SUBS.NEW_ID)+(SELECT COUNT(SUBS_D.NEW_ID)
>FROM SUBS WHERE SUBS.NEW_ID=1;
Same two problems, but if the intent is, as it appears, to sum
all non-null subs rows where subs.new_id is not null
all subs_d rows where new_id matches a row in subs and subs.new_id=1
then perhaps this is what you are looking for...
SELECT COUNT(subs.new_id) + COUNT(subs_d.new_id)
LEFT JOIN subs_d USING(new_id)
> (SELECT count(*) FROM SUBS) -
> (SELECT count(SUBS.S_ID) FROM SUB
> LEFT JOIN SUBS ON SUBS.S_ID = SUB.S_ID) +
> (SELECT count(*) FROM SUBS_D);
I do not think you will be able to combine two COUNT(*) calls for
different tables in one query. Try multiple queries.
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 266.5.2 - Release Date: 2/28/2005