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 f17FQTx64202 for ; Wed, 7 Feb 2001 10:26:29 -0500 (EST) (envelope-from brice@webprojkt.com) Received: (qmail 29825 invoked by uid 604); 7 Feb 2001 15:26:25 -0000 Received: from unknown (HELO webprojkt.com) (216.170.131.123) by morpheus.webprojkt.com with SMTP; 7 Feb 2001 15:26:25 -0000 Message-ID: <3A816921.FEB47B80@webprojkt.com> Date: Wed, 07 Feb 2001 09:26:23 -0600 From: Brice Ruth X-Mailer: Mozilla 4.76 (Macintosh; U; PPC) X-Accept-Language: en MIME-Version: 1.0 To: Michael Fork CC: Ian Harding , pgsql-general@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference? References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200102/329 X-Sequence-Number: 4202 I believe we're getting closer to the problem here. When I run the first query below, I get 0 results, which should be wrong. When I run the query: SELECT length(monographid) FROM tblpemdruglink WHERE drugid='DG-5039'; I get 5 returned to me, even though the string is only 4 ('2008'). However, after doing: UPDATE tblpemdruglink SET monographid=trim(monographid); and rerunning the query above, I still get 5, not 4. Is something being imported incorrectly by the COPY ... FROM? Any way for me to tell if there are other non-printing characters in there? Thank you all for the continued support on this. Regards, Brice Ruth Michael Fork wrote: > > Run the following query: > > SELECT fdb.versionid, fdb.category, pem.drugid FROM tblfdbmono fdb, > tblpemdruglink pem WHERE fdb.monographid = pem.monographid ORDER BY 1, 2, > 3; > > is anything returned? If not, that is your problem (no rows exists with > matching monographid's). If information is returned, however, pick an > arbitrary row, and plug the data into the following query (you will have > a valid where clause then): > > SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono > fdb, tblpemdruglink pem WHERE fdb.monographid = pem.monographid AND > fdb.versionid = '<>' AND fdb.category = '<>' AND > pem.drugid = '<>' > > Also, you may want to try qualifying your table names, i.e.: > > SELECT fdb.sequencenumber, fdb.sectioncode, fdb.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'; > > Michael Fork - CCNA - MCP - A+ > Network Support - Toledo Internet Access - Toledo Ohio > > On Tue, 6 Feb 2001, Brice Ruth wrote: > > > 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/ > > -- Brice Ruth WebProjkt, Inc. VP, Director of Internet Technology http://www.webprojkt.com/