Re: This SQL works under Mysql, not Postgresql.

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> writes:
> On Jan 25, 2008 10:11 AM, acec acec <tomcatacec(at)yahoo(dot)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