how can I do instead of using subselects?
Hello everyone, 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); 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; 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); Another question is how do I do when I want to compare two tables, normally is it no problem to get a list with rows that exist i both tables, but how do I do if I want to to have the diffrence listed instead, the rows that only appears in one of the tables? Thanks in advance! /Joppe -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: how can I do instead of using subselects?
Please help, this must be easy for experinced MySQL users but for me as a newbie isn't it. I have tried to find the answer on the things below in older MySQL manuals but without any success... 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 and works fine in MySQL 4.x.x but not in 3.23.55 where I also need to use them: SELECT (SELECT count(SUB.S_ID) FROM SUB LEFT JOIN SUBS ON SUBS.S_ID = SUB.S_ID) /(SELECT 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; 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); Another question is how do I do when I want to compare two tables, normally is it no problem to get a list with rows that exist i both tables, but how do I do if I want to to have the diffrence listed instead, the rows that only appears in one of the tables? Thanks in advance! /Joppe -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how can I do instead of using subselects?
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fwd: how can I do instead of using subselects?
Joppe, Another question is how do I do when I want to compare two tables, normally is it no problem to get a list with rows that exist i both tables, but how do I do if I want to to have the diffrence listed instead, the rows that only appears in one of the tables? SELECT * FROM tblA LEFT JOIN tblB ON tblA.key=tblB.key WHERE tblB.key IS NULL; returns the rows of tblA for which there is no tblB row with a matching key value. 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
