Обсуждение: 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
 

"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