Gurhan: <snip>
> I was wondering if you'd wanna use temporary tables to accomplish it.. Actually, another person on this list came up with the following: SELECT s1.site_id FROM site_category AS s1 JOIN site_category AS s2 WHERE s1.category_id=10 AND s2.category_id=12 AND s1.site_id=s2.site_id It worked like a charm! Thanks to all who replied! Steve > You may wanna do: > > CREATE TEMPORARY TABLE tmp1 SELECT * FROM site_category WHERE > category_id=$category_id_1; > > ok this will give us the records that matches $category_id_1 in category_id > field put them into a temporary table called tmp1.... Then do: > > CREATE TEMPORARY TABLE tmp2 SELECT * FROM site_category WHERE > category_id=$category_id_2; > > and this will give us the records matching $category_id_2 in category_id > field and put them into a temporary table called tmp2.. > > Now you have 2 temporary tables, tmp1 and tmp2 as shown below: > > tmp1: > +------+------+------+ > | sci | si | ci | > +------+------+------+ > | 1 | 2 | 10 | > | 4 | 4 | 10 | > +------+------+------+ > > tmp2: > +------+------+------+ > | sci | si | ci | > +------+------+------+ > | 3 | 4 | 12 | > | 5 | 5 | 12 | > +------+------+------+ > > Now you can use use join syntax to find the si value thats common to the > both tables... > > select * from tmp1, tmp2 where tmp1.si=tmp2.si; > > Does this work for you?? > > Gurhan > > -----Original Message----- > From: Summit [mailto:[EMAIL PROTECTED]] > Sent: Monday, March 18, 2002 9:22 PM > To: [EMAIL PROTECTED] > Subject: [PHP-DB] SQL statement - PHP/mySQL - brain fart > > For some reason I keep thinking that this should be simple - but I just > can't seem to figure it out. Help??? Please??? [I've been working on > it for two days now.] > > Overview: What I'm trying to do is query one table by passing it two > different variables - and return only the results that are COMMON to > both variables. [PHP 4.1.2/mySQL 3.23.44/FreeBSD] > > Assume I have a site_category table: > > ------------------- > site_category > ------------------- > site_category_id > site_id > category_id > ------------------- > > Perhaps a dump of this looks something like this: > > ---------------- ------- ----------- > site_category_id site_id category_id > ---------------- ------- ----------- > 1 2 10 > 2 3 11 > 3 4 12 > 4 4 10 > 5 5 12 > 6 5 14 > ---------------- ------- ----------- > > Using values for the varibles I'm passing to the query (see below) of > ... > > $category_id_1 = 10 > $category_id_2 = 12 > > ... the result I'm looking for is: > > site_id = 4 > > ... as this is the only site_id which is common to both ... > > category_id = 10 > category_id = 12 > > I've tried a bazillion variations on the following query: > > SELECT sc.* > FROM site_category sc > WHERE (sc.category_id = $category_id_1 OR sc.category_id = > $category_id_2) > > Breaking out the parts ... > > So, if category_id_1 = 10, I'm returned: > > site_id = 2 > site_id = 4 > > So, if category_id_2 = 12, I'm returned: > > site_id = 4 > site_id = 5 > > How can I get that "4" which you can clearly see is common to both of > the parts above? > > But just about no matter how I write my queries, I keep getting: > > site_id = 2 > site_id = 4 > site_id = 4 > site_id = 5 > > Or if use SELECT DISTINCT we get: > > site_id = 2 > site_id = 4 > site_id = 5 > > [I want that extra 4 that the "DISTINCT" threw out!!!] > > I keep thinking that I can do this in a single query - but I don't know > for sure. I've tried sub-selects with no luck [E.g. IN()]. Do I need > to do something with arrays and array_intersect? [I've even tried > messing with the PHP3 hacks for array_unique - trying to reverse them 'n > stuff - but still no luck.] > > Does anyone have a simple solution? [I'll even take a hard solution - > but I keep thinking that I'm just looking at the the wrong way.] > > TIA, > > Summit > > ==================================================== > There is no such thing as a stupid person - > there are only those who choose not to learn! > Summit - [EMAIL PROTECTED] > ==================================================== > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- -------------------------------------------------- Peak to Peak Trail and Wilderness Links Steve Fry - LinkKeeper - [EMAIL PROTECTED] http://www.peaktopeak.net -------------------------------------------------- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php