Hi!
I am not really sure, if I understood your problem correctly. It would
have helped, if you gave an example of how you would have solved what
you want with sub queries.
I assume you want
SELECT RevBy,RevDescription,DwgRev,DwgNumber,DwgTitle
FROM Drawing
WHERE DwgNumber
IN (
SELECT DwgNumber
FROM Drawing
WHERE DwgTitle LIKE '%FLNG%'
)
If you meant something different, please state the whole query with
sub query as example.
The replacement for the sub query would be something like this (comes
just from my mind, so there might be slight typing errors :-):
SELECT a.RevBy,a.RevDescription,a.DwgRev,a.DwgNumber,a.DwgTitle
FROM Drawing=a, Drawing=b
WHERE a.DwgNumber=b.DwgNumber AND
b.DwgTitle LIKE '%FLNG%'
Hope this helps.
Bye,
Benjamin
[...]
> 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
>
>
>
>
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread1432@stripped
>
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.
--
Benjamin Pflugmann aka Philemon philemon@stripped
Programming, Guestbook support voice: +49 941 94 65 939
SPiN GmbH http://www.spin.de fax: +49 941 94 65 938
======= web design - java chats - guestbooks - java/cgi coding =======
Attachment: [application/pgp-signature]
