Thread: This SQL works under Mysql, not Postgresql.
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? Thanks in advance. Looking for the perfect gift? Give the gift of Flickr! http://www.flickr.com/gift/
On Jan 25, 2008 10:11 AM, acec acec <tomcatacec@yahoo.ca> 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...
Please keep replies on list, others may have answers I do not. On Jan 25, 2008 10:29 AM, acec acec <tomcatacec@yahoo.ca> wrote: > --- Scott Marlowe <scott.marlowe@gmail.com> wrote: > > On Jan 25, 2008 10:11 AM, acec acec > > <tomcatacec@yahoo.ca> 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.
Maybe it is not legal sql according to SQL standard, but it works under mysql, I try to port it into Postgresql. --- Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Jan 25, 2008 10:11 AM, acec acec > <tomcatacec@yahoo.ca> 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 BETAat http://ca.messenger.yahoo.com/webmessengerpromo.php
Neither have I.
The LEFT JOIN I know is something like
SELECT ...
FROM table1
LEFT OUTER JOIN table2
ON ....
Try using this construct
Best,
Oliveiros
----- Original Message -----
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "acec acec" <tomcatacec@yahoo.ca>
Cc: <pgsql-sql@postgresql.org>
Sent: Friday, January 25, 2008 4:23 PM
Subject: Re: [SQL] This SQL works under Mysql, not Postgresql.
>> 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...
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
"Scott Marlowe" <scott.marlowe@gmail.com> writes: > On Jan 25, 2008 10:11 AM, acec acec <tomcatacec@yahoo.ca> 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'; > Is that legal SQL? It is not, though given mysql's historical inability to implement the JOIN syntax per-spec, it's not too surprising that they'd show such a weak grasp of correct syntax. Maybe replace the comma with CROSS JOIN? ... (SERVICE suv CROSS JOIN SERVICE sus) ... regards, tom lane