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 f17DfBx50988 for ; Wed, 7 Feb 2001 08:41:11 -0500 (EST) (envelope-from pgsql-general-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f16IUMx24538; Tue, 6 Feb 2001 13:30:22 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f16I74A22533; Tue, 6 Feb 2001 13:07:04 -0500 (EST) (envelope-from news) From: Brice Ruth X-Newsgroups: comp.databases.postgresql.sql, comp.databases.postgresql.general, comp.databases.postgresql.novice Subject: Re: SQL Join - MySQL/PostgreSQL difference? Date: Tue, 06 Feb 2001 12:07:03 -0600 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 104 Message-ID: <3A803D47.7D4C7079@webprojkt.com> References: <3A79E9E8.238F653B@webprojkt.com> <3A7A3B4C.856EC9EF@pakrat.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: scrappy@hub.org To: Ian Harding X-Mailer: Mozilla 4.76 (Macintosh; U; PPC) X-Accept-Language: en To: pgsql-general@postgresql.org.pgsql-sql@postgresql.org X-Archive-Number: 200102/324 X-Sequence-Number: 4197 FYI: Here are the table definitions: CREATE TABLE TBLPEMDRUGLINK ( DRUGID VARCHAR(10) NOT NULL, MONOGRAPHID VARCHAR(10) NOT NULL, CONSTRAINT PK_TBLPEMDRUGLINK PRIMARY KEY (DRUGID, MONOGRAPHID) ); CREATE TABLE TBLFDBMONO ( VERSIONID VARCHAR(10) NOT NULL, CATEGORY VARCHAR(10) NOT NULL, MONOGRAPHID VARCHAR(10) NOT NULL, SEQUENCENUMBER SMALLINT NOT NULL, SECTIONCODE VARCHAR(1), LINETEXT VARCHAR(255), CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY, MONOGRAPHID, SEQUENCENUMBER) ); Running the following query: Query1: SELECT sequencenumber,sectioncode,linetext Query1: FROM tblfdbmono fdb, tblpemdruglink pem WHERE Query1: fdb.monographid=pem.monographid AND Query1: fdb.versionid='FDB-PE' AND Query1: fdb.category='PEM' AND Query1: pem.drugid='DG-5039'; returns 0 rows. However, the following two queries produce results: Query2: SELECT * FROM tblpemdruglink WHERE drugid='DG-5039'; Query3: SELECT * FROM tblfdbmono WHERE Query3: monographid='2008' AND Query3: versionid='FDB-PE' AND Query3: category='PEM'; To my knowledge, Query1 is the join that should produce the same results as the manual join represented by queries 2 & 3. What's going on? -Brice Ian Harding wrote: > > Brice Ruth wrote: > > > Greetings. > > > > I'm working with a product provided by a third part that interfaces to > > data housed in a database of your choice. Previously, my choice was > > MySQL - apparently it handled certain queries too slowly, so I'm giving > > PostgreSQL a shot. Here's the query: > > > > 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. 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. > > > > Any help will be (as always) sincerely appreciated. > > > > -- > > Brice Ruth > > WebProjkt, Inc. > > VP, Director of Internet Technology > > http://www.webprojkt.com/ > > It should work the same in both. The only thing I notice is that not all > the field names are qualified with table names or aliases. That can lead > to ambiguity, but the query would blow up on both databases if that were a > problem. > > Ian -- Brice Ruth WebProjkt, Inc. VP, Director of Internet Technology http://www.webprojkt.com/