Re: This SQL works under Mysql, not Postgresql. - Mailing list pgsql-sql

"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