Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f17Dbtx50314 for ; Wed, 7 Feb 2001 08:37:55 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from morpheus.webprojkt.com (morpheus.webprojkt.com [209.61.155.148]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f16Fw0x04296 for ; Tue, 6 Feb 2001 10:58:01 -0500 (EST) (envelope-from brice@webprojkt.com) Received: (qmail 25546 invoked by uid 604); 6 Feb 2001 15:57:53 -0000 Received: from unknown (HELO webprojkt.com) (216.170.131.123) by morpheus.webprojkt.com with SMTP; 6 Feb 2001 15:57:53 -0000 Message-ID: <3A801F01.E0A4318E@webprojkt.com> Date: Tue, 06 Feb 2001 09:57:53 -0600 From: Brice Ruth X-Mailer: Mozilla 4.76 (Macintosh; U; PPC) X-Accept-Language: en MIME-Version: 1.0 To: Peter Eisentraut CC: pgsql-sql@postgresql.org Subject: Re: SQL Join - MySQL/PostgreSQL difference? References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200102/112 X-Sequence-Number: 1290 Here's some more details: The following query: SELECT * FROM tblpemdruglink WHERE drugid = 'DG-5039'; returns the following results: drugid | monographid ---------+------------- DG-5039 | 2008 (1 row) the following query: SELECT * FROM tblfdbmono WHERE monographid='2008' AND versionid='FDB-PE' AND category='PEM'; returns the following results: versionid | category | monographid | sequencenumber | sectioncode | linetext -----------+----------+-------------+----------------+-------------+--------------------------------------------------------------------- {content} sorry, the {content} is what's under NDA :( In any case, you can see the structure of the tables tblpemdruglink and tblfdbmono from here. Now the following query: SELECT sequencenumber,sectioncode,linetext FROM tblfdbmono fdb, tblpemdruglink pem WHERE fdb.monographid=pem.monographid AND fdb.versionid='FDB-PE' AND fdb.category='PEM' AND pem.drugid='DG-5039'; Should return the same results as the second query above. However, this is what I get: sequencenumber | sectioncode | linetext ----------------+-------------+---------- (0 rows) And it takes about 3-4 seconds to return with that prognosis. What's going on here? -Brice Peter Eisentraut wrote: > > Brice Ruth writes: > > > SELECT > > a.Number, > > a.Code, > > a.Text > > FROM > > b, > > a > > WHERE > > (b.Id = a.Id) AND > > (VersionId = 'key1') AND > > (Category = 'key2') AND > > (b.d_Id = 'key3') > > ORDER BY > > a.Number; > > > > (my apologies: I had to 'mangle' the table/column names because of NDA) > > > > So my question is this: would this query operate differently in MySQL > > than in PostgreSQL? The reason I ask is that this query in MySQL > > returns results, yet in PostgreSQL it does not. > > Without showing the tables and the data in it, it's fairly hard to tell. > I think MySQL does case insensitive string comparisons; check that. > > > I read a post about PostgreSQL not supporting outer joins, but I don't > > have enough experience with SQL to determine if this is such a query > > or not. Please advise. > > This is not an outer join. > > -- > Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/ -- Brice Ruth WebProjkt, Inc. VP, Director of Internet Technology http://www.webprojkt.com/