From: Peter Brawley
Date: February 28 2005 6:31pm
Subject: Re: how can I do instead of using subselects?
List-Archive: http://lists.mysql.com/mysql/180680
Message-Id: <42236378.7080403@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Joppe,

 >I have a problem with a few sql-queries because I have written the 
questions
 >with subselect statements and then will they not work on older MySQL 
DB:s.
 >Can any one help me to say haw to solve it without subselect!

 >Today looks the questions like this:

 >SELECT
 >  (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(*)
FROM sub;

 >SELECT COUNT(SUBS.NEW_ID)+(SELECT COUNT(SUBS_D.NEW_ID)
 >FROM SUBS_D
 >WHERE SUBS.NEW_ID=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
  plus
  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)
FROM subs
LEFT JOIN subs_d USING(new_id)
WHERE subs.new_id=1;

 >SELECT
 >  (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.

Peter Brawley
http://www.artfulsoftware.com



-- 
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