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 f16NUNx54465; Tue, 6 Feb 2001 18:30:23 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f16NN0c63438; Tue, 6 Feb 2001 18:23:00 -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 17:22:59 -0600 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 88 Message-ID: <3A808752.5BF8BB7E@webprojkt.com> References: <3A79E9E8.238F653B@webprojkt.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: scrappy@hub.org 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/283 X-Sequence-Number: 4155 Here's something to chew on ... The following snippet of SQL produces a result: -- 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 INT2 NOT NULL, SECTIONCODE VARCHAR(1), LINETEXT VARCHAR(255), CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY, MONOGRAPHID, SEQUENCENUMBER) ); insert into tblpemdruglink values ('DG-5039','2008'); insert into tblfdbmono values ('FDB-PE','PEM','2008',8,'A','some info to display'); 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'; -- This puts some 'fake' data into the tables, but the fake data resembles the true data. Now, when I load the tables with the true data using: COPY tblpemdruglink FROM '/tmp/Data/db_files/tblPEMDrugLink.txt' USING DELIMITERS '|'; COPY tblfdbmono FROM '/tmp/Data/db_files/tblFDBMono.txt' USING DELIMITERS '|'; and then run the same SELECT as before: 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'; I get 0 results. So the logical conclusion: the dataset doesn't contain a record that satisfies the query. However, the following queries represent the 'manual join' that the join above automates: SELECT * FROM tblpemdruglink WHERE drugid='DG-5039'; > drugid | monographid >---------+------------- > DG-5039 | 2008 >(1 row) SELECT sequencenumber, sectioncode, linetext FROM tblfdbmono fdb WHERE fdb.monographid='2008' AND fdb.versionid='FDB-PE' AND fdb.category='PEM'; > sequencenumber | sectioncode | linetext >----------------+-------------+--------------- >{************** results omitted *************) >(64 rows) So if the 'manual join' produces data yet the actual join does not, then the logical conclusion above doesn't hold and something is wrong (besides the data set). That's where I need help. I can stare at the SQL all day & all night long and not figure anything out ... I'm not an expert at SQL and this is the first time I'm using PostgreSQL. One thing I can say, however, is that mirroring this in MySQL (and allowing for case-sensitivity) produces results (it takes a few seconds because the tblFDBMono table is actually quite large). What's going on?? Any help at all is most sincerely appreciated. Hell, I wouldn't mind coughin' up the $$ for a support incident if I thought that would help ... but I'm not entirely sure where the problem is to warrant a support incident. Please respond to this newsgroup, the mailing list (which should be mirrored on the newsgroup), and/or to me personally. I'm twiddlin' my thumbs until I can figure this one out. Regards, Brice Ruth