From: Stewart Mitchell
Date: April 5 1999 8:44pm
Subject: subquery work around anyone?
List-Archive: http://lists.mysql.com/mysql/1432
Message-Id: <3709209F.B8CAA11C@adms06.fnal.gov>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

hello all,

with subqueries still several months away, is anyone using a work-around

to get the same result that a subselect returns?

we are building a mysql front-end for our engineering drawing database
and
have several queries that follow the form of:

select a
  from t
where b  IN
  (select x
  from s);

the schema is composed of two fact tables and four dimension tables...
the attributes in the dimension table that are causing us trouble are:

CREATE TABLE Drawing

       DwgNumber                INT(11),

       DwgRev                   CHAR(2),

       RevDate                  DATE,
       RevBy                    CHAR(20),

       DwgTitle                 CHAR(80),
       RevDescription           CHAR(20),

);

our "key word" type query would find all revisions(DwgRev) to a drawing
number
(DwgNumber)by searching the drawing title (DwgTitle) or RevDescription
field;

we posit that would not know what the DwgNumber was and could not
include it in our query...data looks like this:

for DwgNumber 359714 (a test case)

EXPAND YOUR READER -WIDE TEXT
+-----------+----------------------------------------------------------+--------+------------+----------+----------------------+

| DwgNumber | DwgTitle                                                 |
DwgRev | RevDate    | RevBy    | RevDescription       |
+-----------+----------------------------------------------------------+--------+------------+----------+----------------------+

|    359714 | MI A1-A150 VS ADD V10 DETAILED COMPONENT ELEVATION VIEWS |
-      | 1998-06-16 | MAVEN    | NO REVISION          |
|    359714 | ROTATED ROUGH PORTS.REM #38 ADD #38 STOCK PLATE          |
A      | 1998-09-23 | EHOLDORF | ROTATED ROUGH PORTS. |
|    359714 | ION PUMP TEES ALL ARE 6 LONG                             |
B      | 1998-10-05 | DSCHMITT | ION PUMP TEES ALL AR |
|    359714 | ADD QD FLNG TO TOROIDS UNITS REM BELLOWS 'N'             |
C      | 1998-10-19 | DSCHMITT | ADD QD FLNG TO TOROI |
|    359714 | CORR ORIENTATION ITEMS #10 AND #12                       |
D      | 1998-10-22 | DSCHMITT | CORR ORIENTATION ITE |
+-----------+----------------------------------------------------------+--------+------------+----------+----------------------+



we want to query DwgTitle field for "FLNG" and return
RevBy,RevDescription,DwgRev,DwgNumber,DwgTitle
from Drawing where DwgTitle like '%FLNG%';

without a subselect my query is
select RevBy,RevDescription,DwgRev,DwgNumber,DwgTitle from Drawing where
DwgTitle like '%FLNG%';

which returns for DwgNumber 359714
EXPAND YOUR READER -WIDE TEXT
+-----------+----------------------+--------+-----------+-----------------------------------------------------------+

| RevBy     | RevDescription       | DwgRev | DwgNumber |
DwgTitle                                                  |
+-----------+----------------------+--------+-----------+-----------------------------------------------------------+

| WESSELN   | NO REVISION          | -      |    359176 | MI VS CF 6 OD
ROTATE-FLNG AND TRANSITION PIECE            |
| WESSELN   | MI VS CF6OD ROTATE-F | A      |    359176 | MI VS CF6OD
ROTATE-FLNG-TRANS CHG TOL TO 1/32             |
| PJFISHER  | MI VS CF6OD ROTATE-F | B      |    359176 | MI VS CF6OD
ROTATE-FLNG TRANS INSER CHG NOW 13/32 WAS.438 |
| PJFISHER  | NO REVISION          | -      |    359183 | MI VS 4.5OD CF
FLNG BLANK ASSY                            |
| PJFISHER  | NO REVISION          | -      |    359184 | MI VS 4.5OD CF
FLNG PIRANI GAUGE ASSY                     |
| SMITCHELL | NO REVISION          | -      |    359185 | MI VS 4.5OD CF
FLNG LETUP VALVE ASSY                      |
| DSCHMITT  | ADD QD FLNG TO TOROI | C      |    359714 | ADD QD FLNG TO
TOROIDS UNITS REM BELLOWS 'N'              |
+-----------+----------------------+--------+-----------+-----------------------------------------------------------+

look closely...mysql only found DwgRev C...it missed A and B, jumped
over C and missed D...a null DwgRev is a -

any idea for a work around or even an IN function? we have thought about
writing to a temporary table, but this
is very rickety...


--
Stewart Mitchell