Re: SQL Join - MySQL/PostgreSQL difference? - Mailing list pgsql-sql

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