Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference? - Mailing list pgsql-general

I believe we're getting closer to the problem here.  When I run the
first query below, I get 0 results, which should be wrong.  When I run
the query:

SELECT length(monographid) FROM tblpemdruglink WHERE drugid='DG-5039';

I get 5 returned to me, even though the string is only 4 ('2008').
However, after doing:

UPDATE tblpemdruglink SET monographid=trim(monographid);

and rerunning the query above, I still get 5, not 4.  Is something being
imported incorrectly by the COPY ... FROM?  Any way for me to tell if
there are other non-printing characters in there?

Thank you all for the continued support on this.

Regards,
Brice Ruth

Michael Fork wrote:
>
> 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/
> >

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/