I think you're doing different join types. SQLite is probably doing CROSS JOINs. PostgreSQL is probably doing at least one INNER JOIN.
From http://www.sqlite.org/lang_select.html: "If multiple tables names are separated by commas, then the query is against the cross join of the various tables." -- Brandon Aiken CS/IT Systems Engineer -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Thomas Peter Sent: Tuesday, September 26, 2006 9:15 AM To: pgsql-general@postgresql.org Subject: [GENERAL] change the order of FROM selection to make query work hi, posted this twice allready, but didn't seem to make it to the list. so one more try: i support a trac [1] installation and migrated the backend from sqlite to postgres 8.1.4, which worked fine, but: the following sql stopped working with postgres, and the fix of this problem seems strange to me. first the old sql, that worked with sqlite: SELECT p.value AS __color__, id AS ticket, summary, status, priority ,component, t.type AS type, time AS created, changetime AS _changetime, description AS _description, reporter AS _reporter FROM ticket as t, permission as perm, enum as p WHERE status IN ('new', 'assigned', 'reopened') AND perm.action = 'mf' and p.name = t.priority AND p.type='priority' ORDER BY priority, type, time and the fix was, to put the 'ticket as t' at the end in the FROM statement. changing FROM ticket as t, permission as perm, enum as p to FROM permission as perm, enum as p, ticket as t works like expected! so is this a bug, or do i get soemthing wrong (a hint where to rtfm would be very welcome in this case) thanx, thomas [1] trac.edgewall.org ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq