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

Run the following query:

SELECT fdb.versionid, fdb.category, pem.drugid FROM tblfdbmono fdb,
tblpemdruglink pem WHERE fdb.monographid = pem.monographid ORDER BY 1, 2,
3;

is anything returned? If not, that is your problem (no rows exists with
matching monographid's).  If information is returned, however, pick an
arbitrary row, and plug the data into the following query (you will have
a valid where clause then):

SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono
fdb, tblpemdruglink pem WHERE fdb.monographid = pem.monographid AND
fdb.versionid = '<<VERSION ID>>' AND fdb.category = '<<CATEGORY>>' AND
pem.drugid = '<<DRUG ID>>'

Also, you may want to try qualifying your table names, i.e.:

SELECT fdb.sequencenumber, fdb.sectioncode, fdb.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';

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Tue, 6 Feb 2001, Brice Ruth wrote:

> FYI:  Here are the table definitions:
>
> 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              SMALLINT NOT NULL,
>     SECTIONCODE                 VARCHAR(1),
>     LINETEXT                    VARCHAR(255),
>     CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY,
> MONOGRAPHID, SEQUENCENUMBER)
> );
>
> Running the following query:
>
> Query1: SELECT sequencenumber,sectioncode,linetext
> Query1:   FROM tblfdbmono fdb, tblpemdruglink pem WHERE
> Query1:    fdb.monographid=pem.monographid AND
> Query1:    fdb.versionid='FDB-PE' AND
> Query1:    fdb.category='PEM' AND
> Query1:    pem.drugid='DG-5039';
>
> returns 0 rows.
>
> However, the following two queries produce results:
>
> Query2: SELECT * FROM tblpemdruglink WHERE drugid='DG-5039';
>
> Query3: SELECT * FROM tblfdbmono WHERE
> Query3:   monographid='2008' AND
> Query3:   versionid='FDB-PE' AND
> Query3:   category='PEM';
>
> To my knowledge, Query1 is the join that should produce the same results
> as the manual join represented by queries 2 & 3.
>
> What's going on?
>
> -Brice
>
> Ian Harding wrote:
> >
> > Brice Ruth wrote:
> >
> > > Greetings.
> > >
> > > I'm working with a product provided by a third part that interfaces to
> > > data housed in a database of your choice.  Previously, my choice was
> > > MySQL - apparently it handled certain queries too slowly, so I'm giving
> > > PostgreSQL a shot.  Here's the query:
> > >
> > > SELECT
> > >   a.Number,
> > >   a.Code,
> > >   a.Text
> > > FROM
> > >   b,
> > >   a
> > > WHERE
> > >   (b.Id = a.Id) AND
> > >   (VersionId = 'key1') AND
> > >   (Category = 'key2') AND
> > >   (b.d_Id = 'key3')
> > > ORDER BY
> > >   a.Number;
> > >
> > > (my apologies: I had to 'mangle' the table/column names because of NDA)
> > >
> > > So my question is this: would this query operate differently in MySQL
> > > than in PostgreSQL?  The reason I ask is that this query in MySQL
> > > returns results, yet in PostgreSQL it does not.  I read a post about
> > > PostgreSQL not supporting outer joins, but I don't have enough
> > > experience with SQL to determine if this is such a query or not.  Please
> > >
> > > advise.
> > >
> > > Any help will be (as always) sincerely appreciated.
> > >
> > > --
> > > Brice Ruth
> > > WebProjkt, Inc.
> > > VP, Director of Internet Technology
> > > http://www.webprojkt.com/
> >
> > It should work the same in both.  The only thing I notice is that not all
> > the field names are qualified with table names or aliases.  That can lead
> > to ambiguity, but the query would blow up on both databases if that were a
> > problem.
> >
> > Ian
>
> --
> Brice Ruth
> WebProjkt, Inc.
> VP, Director of Internet Technology
> http://www.webprojkt.com/
>