On Tuesday 19 March 2002 10:22, Summit wrote: > 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.]
SELECT sc.* FROM site_category sc WHERE sc.category_id = $category_id_1 AND sc.category_id = $category_id_2 Or am I missing something? -- Jason Wong -> Gremlins Associates -> www.gremlins.com.hk /* Let's just be friends and make no special effort to ever see each other again. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php