pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (29+ messages)  latest in thread

Message-ID: <3A808752.5BF8BB7E@webprojkt.com>
Permalink:  ../3A808752.5BF8BB7E@webprojkt.com/
Also on:    postgresql.org/message-id/3A808752.5BF8BB7E@webprojkt.com

 · 

reply

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