pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedThis SQL works under Mysql, not Postgresql.
6+ messages / 4 participants
[nested] [flat]
* This SQL works under Mysql, not Postgresql.
@ 2008-01-25 16:11 acec acec <tomcatacec@yahoo.ca>
2008-01-25 16:23 ` Re: This SQL works under Mysql, not Postgresql. Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: acec acec @ 2008-01-25 16:11 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: This SQL works under Mysql, not Postgresql.
2008-01-25 16:11 This SQL works under Mysql, not Postgresql. acec acec <tomcatacec@yahoo.ca>
@ 2008-01-25 16:23 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-25 16:32 ` Re: This SQL works under Mysql, not Postgresql. Scott Marlowe <scott.marlowe@gmail.com>
2008-01-25 16:42 ` Re: This SQL works under Mysql, not Postgresql. acec acec <tomcatacec@yahoo.ca>
2008-01-25 16:52 ` Re: This SQL works under Mysql, not Postgresql. Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2008-01-25 17:20 ` Re: This SQL works under Mysql, not Postgresql. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 4 replies; 6+ messages in thread
From: Scott Marlowe @ 2008-01-25 16:23 UTC (permalink / raw)
To: acec acec <tomcatacec@yahoo.ca>; +Cc: pgsql-sql
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...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: This SQL works under Mysql, not Postgresql.
2008-01-25 16:11 This SQL works under Mysql, not Postgresql. acec acec <tomcatacec@yahoo.ca>
2008-01-25 16:23 ` Re: This SQL works under Mysql, not Postgresql. Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-01-25 16:32 ` Scott Marlowe <scott.marlowe@gmail.com>
3 siblings, 0 replies; 6+ messages in thread
From: Scott Marlowe @ 2008-01-25 16:32 UTC (permalink / raw)
To: acec acec <tomcatacec@yahoo.ca>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: This SQL works under Mysql, not Postgresql.
2008-01-25 16:11 This SQL works under Mysql, not Postgresql. acec acec <tomcatacec@yahoo.ca>
2008-01-25 16:23 ` Re: This SQL works under Mysql, not Postgresql. Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-01-25 16:42 ` acec acec <tomcatacec@yahoo.ca>
3 siblings, 0 replies; 6+ messages in thread
From: acec acec @ 2008-01-25 16:42 UTC (permalink / raw)
To: pgsql-sql
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 BETA at http://ca.messenger.yahoo.com/webmessengerpromo.php
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: This SQL works under Mysql, not Postgresql.
2008-01-25 16:11 This SQL works under Mysql, not Postgresql. acec acec <tomcatacec@yahoo.ca>
2008-01-25 16:23 ` Re: This SQL works under Mysql, not Postgresql. Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-01-25 16:52 ` Oliveiros Cristina <oliveiros.cristina@marktest.pt>
3 siblings, 0 replies; 6+ messages in thread
From: Oliveiros Cristina @ 2008-01-25 16:52 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; acec acec <tomcatacec@yahoo.ca>; +Cc: pgsql-sql
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.
> 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...
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: This SQL works under Mysql, not Postgresql.
2008-01-25 16:11 This SQL works under Mysql, not Postgresql. acec acec <tomcatacec@yahoo.ca>
2008-01-25 16:23 ` Re: This SQL works under Mysql, not Postgresql. Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-01-25 17:20 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2008-01-25 17:20 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: acec acec <tomcatacec@yahoo.ca>; 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2008-01-25 17:20 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-25 16:11 This SQL works under Mysql, not Postgresql. acec acec <tomcatacec@yahoo.ca>
2008-01-25 16:23 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-25 16:32 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-25 16:42 ` acec acec <tomcatacec@yahoo.ca>
2008-01-25 16:52 ` Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2008-01-25 17:20 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox