Re: SQL Join - MySQL/PostgreSQL difference?

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