From: Brice Ruth <brice@webprojkt.com>
To: pgsql-general@postgresql.org.pgsql-sql
Subject: Re: SQL Join - MySQL/PostgreSQL difference?
Date: Tue, 06 Feb 2001 17:22:59 -0600
Message-ID: <3A808752.5BF8BB7E@webprojkt.com> (raw)
References: <3A79E9E8.238F653B@webprojkt.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: brice@webprojkt.com, pgsql-general@postgresql.org.pgsql-sql
Subject: Re: SQL Join - MySQL/PostgreSQL difference?
In-Reply-To: <3A808752.5BF8BB7E@webprojkt.com>
References: <3A79E9E8.238F653B@webprojkt.com> <3A808752.5BF8BB7E@webprojkt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox