List:General Discussion« Previous MessageNext Message »
From:Benjamin Pflugmann Date:April 6 1999 4:18pm
Subject:Re: subquery work around anyone?
View as plain text  
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]
Thread
subquery work around anyone?Stewart Mitchell5 Apr
  • Re: subquery work around anyone?Benjamin Pflugmann6 Apr