From: Martin Gainty
Date: January 1 2009 2:46pm
Subject: RE: Can a JOIN statement do this?
List-Archive: http://lists.mysql.com/mysql/215789
Message-Id: <BLU142-W30CC0E19224A596CE78D81AEE50@phx.gbl>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="_0842d990-c966-450f-9e5a-56e7e9c3904d_"

--_0842d990-c966-450f-9e5a-56e7e9c3904d_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Happy New Year Mike-

JOIN USING(colId) and or JOIN ON(table1.colId=3Dtable2.colIdentifier) will =
preserve the use of Indexes which is faster

LIKE casts off Indexes and does a FTS

HTH
Martin=20
Any recommendations on non-PHP Forum Software for Apache?

______________________________________________=20
Disclaimer and confidentiality note=20
Everything in this e-mail and any attachments relates to the official busin=
ess of Sender. This transmission is of a confidential nature and Sender doe=
s not endorse distribution to any party other than intended recipient. Send=
er does not necessarily endorse content contained within this transmission.=
=20




> Date: Thu=2C 1 Jan 2009 11:28:51 +0800
> From: mikesz@stripped
> To: mysql@stripped
> Subject: Can a JOIN statement do this?
>=20
> Hello mysql and Happy New Year=2C
>=20
> I am working with a Forum database. It contains a forums table=2C a
> posts table and a threads table. Some of the posts contain flash
> objects that I can find using a query like this one:
>=20
> SELECT `pagetext`=2C `postid` FROM `post` WHERE `pagetext` LIKE
> '%someuniqueidentifier%'ORDER BY `postid` DESC
>=20
> This query works fine for what I needed. Now=2C the requirement has
> changed to finding that latest object posted in a specific forum but
> the forum table has no direct reference to the postid. The thread
> table has a reference to the forumID but not a postID.
>=20
> It looked something like this:
> posts table:
>=20
> +---------+------------+
> | post_id | thread_id  |
> +--------+-------------+
>=20
> forum table:
>=20
> +----------+------------+
> | forum_id |            |
> +----------+------------+
>=20
> thread table:
>=20
> +------------+----------+
> | thread_id  | forum_id |
> +------------+----------+
>=20
> I know the forum ID that contains the objects but need to query the
> threads to see which ones contain posts with objects. Then grab the
> last one for processing.
>=20
> So I think I have at least two queries now instead of the one I used to g=
rab
> that latest objects from the database.
>=20
> If I did manual queries=2C I would select the latest thread_id posted to
> forum_id and then a second query to find all the posts in that thread
> that contained objects and grab the last one posted.
>=20
> I think this all might be combined with a join but I am not clear
> about how to do that because the conditional seems to need a result
> that I don't have until I run the first query=2C i.e. WHERE
> `forum_id`=3D'163' AND `thread.thread_id`=3D (results of query to find
> last thread) AND `pagetext` LIKE %someobjectidentifier% DESC LIMIT 1
>=20
> So=2C I am still wondering if using JOIN is the right path to purse to
> optimize this query.
>=20
> Any suggestion greatly appreciated.
>=20
>=20
> --=20
> Best regards=2C
>  mikesz                          mailto:mikesz@stripped
>=20
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=3Dmgainty@stripped=
om
>=20

_________________________________________________________________
Send e-mail anywhere. No map=2C no compass.
http://windowslive.com/oneline/hotmail?ocid=3DTXT_TAGLM_WL_hotmail_acq_anyw=
here_122008=

--_0842d990-c966-450f-9e5a-56e7e9c3904d_--