Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 3DDC92E2F32 for ; Fri, 25 Jan 2008 12:32:30 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 75782-07 for ; Fri, 25 Jan 2008 12:32:24 -0400 (AST) Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.226]) by postgresql.org (Postfix) with ESMTP id 02A842E2FB3 for ; Fri, 25 Jan 2008 12:32:23 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id 58so440862wri.10 for ; Fri, 25 Jan 2008 08:32:23 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=MYKy827LwF9DJZDuG4R36N5q6xZ3EaYC5HINpMVbhAE=; b=uPQ7AcERwD+ji0MBP7VR20c+aeeekf7ACQbHibDlRjx7S5R+bsHBt+/ToAxDEVpKbzGQenICGRsh/c2O9ovsEAQwPRH/fPJTpHlDI6UjuC+vCJvmpCjPF2BH7ughljzj9mMHj3zIDlY7Gy+upDe0rLthSLLw0hgAb27QQ5nciBY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=sahcw2W+fZYh8BfyUIzbzC9rYDT5fl6OxjOy/lt8DPkEgEYkM6Nwe6iTAh0aKR6UvfNoxvIhNfiWbxkSLkyJ/zIlqqPZ/6diXoxr752mD92v6xkTY3duYG8hEU6dTBDLgot+fV1BuqQyZfAoSJ91gnhC62Um5eeg3tT/+8Q1NVw= Received: by 10.142.143.7 with SMTP id q7mr1348523wfd.3.1201278742548; Fri, 25 Jan 2008 08:32:22 -0800 (PST) Received: by 10.142.165.8 with HTTP; Fri, 25 Jan 2008 08:32:22 -0800 (PST) Message-ID: Date: Fri, 25 Jan 2008 10:32:22 -0600 From: "Scott Marlowe" To: "acec acec" Subject: Re: This SQL works under Mysql, not Postgresql. Cc: "SQL Postgresql List" In-Reply-To: <739387.39197.qm@web63012.mail.re1.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <739387.39197.qm@web63012.mail.re1.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/197 X-Sequence-Number: 30050 Please keep replies on list, others may have answers I do not. On Jan 25, 2008 10:29 AM, acec acec wrote: > --- 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... > > > Maybe it is not legal sql according to SQL standard, > but it works under mysql, I try to port it into > Postgresql. Well, I'd say just breaking each part of the "LEFT JOIN (SERVICE suv, SERVICE sus)" and the on () clause should work.