Small select question... (getting bigger!)

[Bryan Coon <bcoon@xxxxxxxxxxxx>]

I wondered the same thing (I didnt write the original query).  But using
'or' will not select rows with null values as using the outer join (+) in
oracle does.  Hence, incorrect rows.

Heres the original Oracle query:

select distinct subsnp_pk, chrom, chrompos, locus, locusid, source,
sourceid, a1freq, status, sampleid  from subsnp, chromosome_position c,
assay_validation, locus_annotation l, sample_info where subsnp_pk = c.snp_fk
(+) and subsnp_pk = l.snp_fk (+) and sourceid = assayid (+) and sample_fk =
sample_pk (+) and source = '10Q' order by subsnp_pk;

First, I didn't write this.  Second, it seems like madness to me.  Oracle
can somehow manage it on NT (probably due to the robust memory of the
machine it was on) but it blows up MySQL on a machine with 256mb ram.  No
supprise with that many joins.

I need to find a way to cleanly, efficiently and beautifully duplicate port
this query to mysql, and would appreciate any thoughts or insights on how to
do this.

Thanks!


-----Original Message-----
From: Geoff Coffey [mailto:gcoffey@xxxxxxxxxxx]
Sent: Tuesday, March 20, 2001 12:52 PM
To: Bryan Coon; 'Jason Landry'; mysql@xxxxxxxxxxxxxxx
Subject: Re: Small select question...


on 3/20/01 1:20 PM, Bryan Coon at bcoon@xxxxxxxxxxxx wrote:

> Sorry for the ambiguity, I was trying to make a really generic select
> statement.  I just meant that if 'where a = A.a' has no matches, but
'where
> b = B.b' or 'where c = C.c' does match, the query returns what did match,
> and just returns null where it didnt.  I will look at the CASE
WHEN...THEN.

How is this not just:

 ... Where a = A.a or b = B.b or c=C.c ...

Thanks,

Geoff

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <mysql-thread68924@xxxxxxxxxxxxxxx>
To unsubscribe, e-mail <mysql-unsubscribe-treed=ultraviolet.org@xxxxxxxxxxxxxxx>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php