Re: Re: SQL Join - MySQL/PostgreSQL difference?

You could try this to see if it makes a difference

UPDATE tblpemdruglink SET monographid = substr(monographid, 1,
length(monographid) - 1)

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

On Wed, 7 Feb 2001, Brice Ruth wrote:

> 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/
>