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

From: Brice Ruth <brice(at)webprojkt(dot)com>
To: Michael Fork <mfork(at)toledolink(dot)com>
Cc: Ian Harding <iharding(at)pakrat(dot)com>, pgsql-general(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
Date: 2001-02-07 15:26:23
Message-ID: 3A816921.FEB47B80@webprojkt.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-sql

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/

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joerg Hessdoerfer 2001-02-07 15:32:55 Re: Not using index
Previous Message Tom Lane 2001-02-07 15:26:05 Re: Not using index

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2001-02-07 15:43:25 Re: Load or Copy ??
Previous Message Patrik Kudo 2001-02-07 15:18:15 Re: Load or Copy ??