Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 02A192E008C for ; Fri, 25 Jan 2008 12:42:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 59913-04 for ; Fri, 25 Jan 2008 12:42:30 -0400 (AST) Received: from web63015.mail.re1.yahoo.com (web63015.mail.re1.yahoo.com [69.147.96.242]) by postgresql.org (Postfix) with SMTP id B9FAC2E3835 for ; Fri, 25 Jan 2008 12:42:22 -0400 (AST) Received: (qmail 93542 invoked by uid 60001); 25 Jan 2008 16:42:22 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.ca; h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=e8Ax6yUv10PVtkiuNNvceSsB3actFOBtJDx+VdlGDfnND1vKW6+Jr4BT3/Z8s+tNsmmKu0yTSQYo665Lh4auXfzyNLd6n11G07EgZJ2KlOJW9jy0hvgQp9h8cp7Oygapqe9DMRlYj1f7aLrX/+9DhZY7rkOJqpqYqJtwZYWFLU0=; X-YMail-OSG: uadctvwVM1mprgcg4vj6sT3oU24OxCCwB26zCJWeXKHVWlNgcXmT74MY23hG4jkGeKj8.UL8fl2kkISruuddfZ_e1OhAMjTmCxMi Received: from [66.46.85.2] by web63015.mail.re1.yahoo.com via HTTP; Fri, 25 Jan 2008 11:42:21 EST Date: Fri, 25 Jan 2008 11:42:21 -0500 (EST) From: acec acec Subject: Re: This SQL works under Mysql, not Postgresql. To: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Message-ID: <979861.91164.qm@web63015.mail.re1.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/198 X-Sequence-Number: 30051 Maybe it is not legal sql according to SQL standard, but it works under mysql, I try to port it into Postgresql. --- Scott Marlowe wrote: > On Jan 25, 2008 10:11 AM, acec acec > wrote: > > I have the following sql, which works fine under > mysql > > database: > > SELECT sa.ID, suv.TOTAL as VOICE_TOTAL, sus.TOTAL > as > > SMS_TOTAL FROM SUB_ACCOUNT sa INNER JOIN > SUBSCRIBER s > > ON (sa.ID = s.SUB_ACCOUNT_ID) LEFT JOIN (SERVICE > suv, > > SERVICE sus) ON (sa.ID = suv.SUB_ACC_ID AND > > suv.SERVICE_ID = 0 AND sa.ID = sus.SUB_ACC_ID AND > > sus.SERVICE_ID = 1) WHERE s.TELEPHONE = > '111111111'; > > When I ran it under postgresql, which gave me > "ERROR: > > syntax error at or near" > > It looks like I could not put two table on LEFT > JOIN: > > LEFT JOIN (SERVICE suv, SERVICE sus) > > > > Do you have any suggestion for this problem? > > Is that legal SQL? I've never seen anything like > that before... > Connect with friends from any web browser - no download required. Try the new Yahoo! Canada Messenger for the Web BETA at http://ca.messenger.yahoo.com/webmessengerpromo.php