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
