From: Benjamin Pflugmann Date: April 6 1999 4:18pm Subject: Re: subquery work around anyone? List-Archive: http://lists.mysql.com/mysql/1464 Message-Id: <19990406181840.J18816@spin.de> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="Rex5+51txc1ort/q"; micalg=pgp-md5; protocol="application/pgp-signature" --Rex5+51txc1ort/q Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable 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=20 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=3Da, Drawing=3Db WHERE a.DwgNumber=3Db.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? >=20 > we are building a mysql front-end for our engineering drawing database > and > have several queries that follow the form of: >=20 > select a > from t > where b IN > (select x > from s); >=20 > the schema is composed of two fact tables and four dimension tables... > the attributes in the dimension table that are causing us trouble are: >=20 > CREATE TABLE Drawing >=20 > DwgNumber INT(11), >=20 > DwgRev CHAR(2), >=20 > RevDate DATE, > RevBy CHAR(20), >=20 > DwgTitle CHAR(80), > RevDescription CHAR(20), >=20 > ); >=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; >=20 > we posit that would not know what the DwgNumber was and could not > include it in our query...data looks like this: >=20 > for DwgNumber 359714 (a test case) >=20 > 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 | > +-----------+----------------------------------------------------------+-= -------+------------+----------+----------------------+ >=20 > we want to query DwgTitle field for "FLNG" and return > RevBy,RevDescription,DwgRev,DwgNumber,DwgTitle > from Drawing where DwgTitle like '%FLNG%'; >=20 > without a subselect my query is > select RevBy,RevDescription,DwgRev,DwgNumber,DwgTitle from Drawing where > DwgTitle like '%FLNG%'; >=20 > which returns for DwgNumber 359714 > EXPAND YOUR READER -WIDE TEXT > +-----------+----------------------+--------+-----------+----------------= -------------------------------------------+=20 > | RevBy | RevDescription | DwgRev | DwgNumber | DwgTitle = | > +-----------+----------------------+--------+-----------+----------------= -------------------------------------------+ > | WESSELN | NO REVISION | - | 359176 | MI VS CF 6 OD R= OTATE-FLNG AND TRANSITION PIECE | > | WESSELN | MI VS CF6OD ROTATE-F | A | 359176 | MI VS CF6OD ROT= ATE-FLNG-TRANS CHG TOL TO 1/32 | > | PJFISHER | MI VS CF6OD ROTATE-F | B | 359176 | MI VS CF6OD ROT= ATE-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' | > +-----------+----------------------+--------+-----------+----------------= -------------------------------------------+ >=20 > look closely...mysql only found DwgRev C...it missed A and B, jumped > over C and missed D...a null DwgRev is a - >=20 > 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... >=20 >=20 > -- > Stewart Mitchell >=20 >=20 >=20 >=20 > --------------------------------------------------------------------- > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before > posting. To request this thread, e-mail mysql-thread1432@stripped >=20 > 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. --=20 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 =3D=3D=3D=3D=3D=3D=3D web design - java chats - guestbooks - java/cgi codin= g =3D=3D=3D=3D=3D=3D=3D --Rex5+51txc1ort/q Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v0.9.5 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE3CjPgmTjUS0P7KdYRAbBJAJ9CFiB5ontt4KF3uChi3+XKWkCWhQCfQTLn qOJ8sc42/NcUFhojQeBpnXg= =pqiU -----END PGP SIGNATURE----- --Rex5+51txc1ort/q--