ok it is 1:37 am here and i have been up since 6:00 am (the day before) and have to be awake ths mornign again at 6:00 am so if I make any mistakes trying to answer your question, please take it easy:)
I was wondering if you'd wanna use temporary tables to accomplish it.. 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
